INTERACT FORUM

Please login or register.

Login with username, password and session length
Advanced search  
Pages: [1]   Go Down

Author Topic: How to: Calculated field for # of Imported Days Range?  (Read 3159 times)

sbsp2

  • Regular Member
  • Galactic Citizen
  • ****
  • Posts: 402
How to: Calculated field for # of Imported Days Range?
« on: November 05, 2006, 11:11:00 pm »

I've tried with MC 11 and MC 12 to create a user calculated field that contains a range of imported days, but I could never get it to work.  I want something like: 0-15 days, 16-30 days, 31-60 days and over 60 days.

Perhaps a guru could help:

Here's the expression I'm trying currently (tried many):

if([Date Imported]=<=15d, 0-15 days, if([Date Imported]=<=30d, 16-30 days, if([Date Imported]=<=60d, 31-60 days, Over 60 day)))

All I ever get is "0-15 days" (which is not correct).

What's wrong?  Or, is this another MC expression logic bug?  Honestly, it appears MC is bugged as MC does not care what you make the first 'if's comparison operator with [Date Imported], it always does the 'True' action.
Logged

marko

  • MC Beta Team
  • Citizen of the Universe
  • *****
  • Posts: 8967
Re: How to: Calculated field for # of Imported Days Range?
« Reply #1 on: November 06, 2006, 02:38:59 am »

what you're actually trying to do is:

if(isequal([date imported],<=15d,8),0-15 days,if(isequal([date imported], etc. etc. etc.

------------------------------
Unfortunately, we cannot do that with date related fields because the database actually contains a long string of numbers for these fields, called "the raw data" and MC translates these numbers into something meaningful for us when we ask to view it. To see an example of what I'm talking about, select a photo and have a look at its 'format' properties in the tag window. You should see something like: <Date>38731.846655092595</Date> which MC translates as 14/01/2006 20:19

For [date imported], we would need to know what the raw data was, then build around that, which is not really very practical, so, we should look to see how else we can get at what you're after.

Many moons ago now, I made 4 smartlists, popped them into a playlist group and used that group as a pane in a "recently imported" viewscheme,

and this approach still works today. Naturally, you could include as many playlists as you wish, though you should number them to keep them in a logical order when displayed in a pane, and not too many as it will make the view scheme too slow to use.

I'm currently using a calendar type viewscheme for all media imported:


Year, Month and Day are all expression based panes:

Year: FormatDate([Date Imported,0], yyyy)
Month: FormatDate([Date Imported,0], MMMM)&DataType=[Month]
Day: FormatDate([Date Imported,0], dd)

You could also use the formatdate expression to get "time ago" values:

formatdate([date imported,0],elapsed) ago

Though I don't like using those in panes as MC cannot sort the values correctly, and you get intermingled days, years and minutes and hours.
---------------------------
Hopefully, that's made things a little clearer for you.
If you really want "0-15 days ago" style grouping, you'll need to use the playlist group method. I'm undecided at the moment which I prefer best between that, or the calender view method. I'm leaning towards the calendar view, but that could just be because it's "newer". Time will tell.

-marko.

Alex B

  • MC Beta Team
  • Citizen of the Universe
  • *****
  • Posts: 10121
  • The Cosmic Bird
Re: How to: Calculated field for # of Imported Days Range?
« Reply #2 on: November 06, 2006, 03:20:06 am »

... Unfortunately, we cannot do that with date related fields because the database actually contains a long string of numbers for these fields, called "the raw data" and MC translates these numbers into something meaningful for us when we ask to view it. To see an example of what I'm talking about, select a photo and have a look at its 'format' properties in the tag window. You should see something like: <Date>38731.846655092595</Date> which MC translates as 14/01/2006 20:19

For [date imported], we would need to know what the raw data was, then build around that, which is not really very practical, so, we should look to see how else we can get at what you're after. ...


From the archive...

Q:
I'm looking for a way to build a smartlist that shows the [last played] before a certain date.

So eg. show me all songs last played before 2005/01/18

I tried the following expression:

FormatDate([Last Played,0],yyyyMMdd)&DataType=[Number] <= 20050118

but this shows me no results

Anyone could help ?

A:
It seems that MC's calendar starts from January 1st 1970, 2:00 am. Internally MC counts seconds so the search expression would be:
[Last Played]=<1105999200

Easy as pie, isn't it.  :)

(I don't know if MC accepts any other time format. If it doesn't, a usable date format should be added.)


Edit:

Obviously the "2:00 am" part is included because of my time zone. I found this explanation for the "January 1st, 1970" starting date:

"The UNIX timestamp is just an integer of the number of seconds since January
1st, 1970. Therefore, you can add, subtract, divide, multiply to your
heart's content and keep it accurate down to a second. There's no need to
do any kind of sanity checks on the integrity of the data since the PHP
date() and mktime() functions do all of that for you. The mktime() function
is the most useful since it allows some seriously easy time/date arithmatic
using any combination of time units (ie. adding two months to 30 seconds)."



This may be useful for programmers, but not for us mortals.
Logged
The Cosmic Bird - a triple merger of galaxies: http://eso.org/public/news/eso0755

hit_ny

  • Citizen of the Universe
  • *****
  • Posts: 3310
  • nothing more to say...
Re: How to: Calculated field for # of Imported Days Range?
« Reply #3 on: November 06, 2006, 04:29:16 am »

I'm leaning towards the calendar view, but that could just be because it's "newer". Time will tell.
Thx for this. Both approaches have their uses.

For day to day, i use the time elapsed.
Logged

sbsp2

  • Regular Member
  • Galactic Citizen
  • ****
  • Posts: 402
Re: How to: Calculated field for # of Imported Days Range?
« Reply #4 on: November 06, 2006, 06:43:33 am »

Ok.

I currently use multiple view schemes (not playlists) to do this, with a qualifier on each like this:

[Date Imported]=<=5d
[Date Imported]=6d-15d
[Date Imported]=16d-30d
[Date Imported]=31d-60d
[Date Imported]=61d-90d
[Date Imported]=>=91d

I like using view schemes because I can customize the display style much more.  Since this expression does work, I figured I could make a user-defined field that did the same and have one nice view scheme for the multiple view schemes.  I still think there's a bug in MC somewhere.
Logged

hit_ny

  • Citizen of the Universe
  • *****
  • Posts: 3310
  • nothing more to say...
Re: How to: Calculated field for # of Imported Days Range?
« Reply #5 on: November 06, 2006, 07:13:33 am »

There is no bug in MC if i understand what you are doing. You need to create as many smartlists as you have date imported entries above. Put them all in a smartlist group.

Then when you create the viewscheme, for the first pane, just select playlist, and locate the smartlist group and add it.

It will take longer to display (than separate view schemes as you have currently) as MC has to go through all the smartlists sequentially before displaying a result.
Logged

sbsp2

  • Regular Member
  • Galactic Citizen
  • ****
  • Posts: 402
Re: How to: Calculated field for # of Imported Days Range?
« Reply #6 on: November 06, 2006, 07:29:35 am »

There is no bug in MC if i understand what you are doing. You need to create as many smartlists as you have date imported entries above. Put them all in a smartlist group.

Then when you create the viewscheme, for the first pane, just select playlist, and locate the smartlist group and add it.

It will take longer to display (than separate view schemes as you have currently) as MC has to go through all the smartlists sequentially before displaying a result.

Ok.  Thanks.  I know what you mean and I've done that before (for some cool handhelds syncing I use).  I still think the if(...) syntax and Date Imported idea should work, so I'm still going to hack at it a bit.  Maybe Matt the programmer can chime in and comment (good or bad).
Logged

sbsp2

  • Regular Member
  • Galactic Citizen
  • ****
  • Posts: 402
Re: How to: Calculated field for # of Imported Days Range?
« Reply #7 on: November 06, 2006, 08:00:03 am »

I've also tried just creating a pane column expression using (for a test):

if(IsRange([Date Imported], 0d-15d), 0-15 days, over 15 days)

and MC can't handle this right either.  MC only displays 0-15 days and All.  over 15 days doesn't even appear.  I think something is bugged with the expression support as I've seen many instances where the 'else' string does not appear (on screen).  Plus, Date Imported doesn't seem to be supported correctly (in expressions).
Logged

marko

  • MC Beta Team
  • Citizen of the Universe
  • *****
  • Posts: 8967
Re: How to: Calculated field for # of Imported Days Range?
« Reply #8 on: November 06, 2006, 09:13:23 am »

As already stated, expressions work on the raw data. So, if you know what the range is, in the number of seconds from 01/01/1970 to the current day, and you use those numbers, the expression will work as you expect, but only for today.

Tomorrow, you'd need to use new numbers. So you see, it's not a bug, it's just the way MC handles "time ago" fields.

sbsp2

  • Regular Member
  • Galactic Citizen
  • ****
  • Posts: 402
Re: How to: Calculated field for # of Imported Days Range?
« Reply #9 on: November 06, 2006, 09:20:27 am »

As already stated, expressions work on the raw data. So, if you know what the range is, in the number of seconds from 01/01/1970 to the current day, and you use those numbers, the expression will work as you expect, but only for today.

Tomorrow, you'd need to use new numbers. So you see, it's not a bug, it's just the way MC handles "time ago" fields.

Also, as I stated before, expressions with Date Imported DOES WORK for view schemes (in the Step 4 area), just not user-defined fields or view scheme column/pane expressions (Step 1 area).  Honestly, I think it's bugged (but that's my opinion).
Logged

marko

  • MC Beta Team
  • Citizen of the Universe
  • *****
  • Posts: 8967
Re: How to: Calculated field for # of Imported Days Range?
« Reply #10 on: November 06, 2006, 10:11:52 am »

I'm curious...
could you post a complete example that works in 'Step 4'?

Edit:
If you mean these:
Quote
[Date Imported]=<=5d
[Date Imported]=6d-15d
[Date Imported]=16d-30d
[Date Imported]=31d-60d
[Date Imported]=61d-90d
[Date Imported]=>=91d
They are not "expressions" as MC calls them, that is the syntax to use when we want MC to perform "time ago" searches for us. It's not possible to take one of those strings and place it into an expression equation.
Take this example you posted earlier:
if(IsRange([Date Imported], 0d-15d)
When the expression evaluator sees this, it will literally look for "0d-15d" in the [date imported] field, but all it will ever find there is a massive number, (the number of seconds from 01/01/1970 to today) and it doesn't know what to do. I guess the bug, if there really must be one, is that MC allows the expression to be sent to the evaluator in the first place, rather than warn the user that it cannot process the expression.

sbsp2

  • Regular Member
  • Galactic Citizen
  • ****
  • Posts: 402
Re: How to: Calculated field for # of Imported Days Range?
« Reply #11 on: November 06, 2006, 10:18:11 am »

I'm curious...
could you post a complete example that works in 'Step 4'?

Any one of these is EXACTLY what I have (in separate view schemes, from above):

[Date Imported]=<=5d
[Date Imported]=6d-15d
[Date Imported]=16d-30d
[Date Imported]=31d-60d
[Date Imported]=61d-90d
[Date Imported]=>=91d
Logged

marko

  • MC Beta Team
  • Citizen of the Universe
  • *****
  • Posts: 8967
Re: How to: Calculated field for # of Imported Days Range?
« Reply #12 on: November 06, 2006, 10:20:57 am »

they're not really expressions though. see my edit above.

sbsp2

  • Regular Member
  • Galactic Citizen
  • ****
  • Posts: 402
Re: How to: Calculated field for # of Imported Days Range?
« Reply #13 on: November 06, 2006, 11:30:33 am »

they're not really expressions though. see my edit above.

Ok...sorry.  Well, maybe it's an easy enhancement for MC to support it throughout (in user-defined fields and wherever expressions are valid).  Or, even a new expression with syntax like IsRangeElaspedDays( <any date field>, <days range> ) or something similiar that returned 1 or 0.  I doubt it'd be that hard to implement.
Logged
Pages: [1]   Go Up