Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[Humbledore]

Hi,
How do I easiest compare a MC date field with a value like '2013-01-04' using a greater or less than condition, in order to create a custom date field based on that comparison?
Thanks!

[MrC]

If your date field is a date type, you can compare the raw formats directly:

   Compare([date,0], >, [your date field,0])

Otherwise, use ConvertDate() first against your date string.

[Humbledore]

Compare([date,0], >, [your date field,0])

What does the '0' in the expression mean?

Anyway, I have succeeded to create a new date field 'Date Recent' using this expression:

If(Compare([Date Imported,0], >, ConvertDate(2013-04-01,0)), [Date Imported], [Date Created])

The test works and it looks correct when displaying the column in the library, even sorting is working in the library view. BUT, there is something that causes the sort function to fail in the Smartlist I've created. It just doesn't accept the sort. What could be the issue here? 

Thanks!

[MrC]

Search for references to raw here:

    http://wiki.jriver.com/index.php/Media_Center_expression_language

How is your sort-failing smartlist defined?

[Humbledore]

I recreated the field using another name, Date Conditional Imported, and now the Smartlist accepts the sorting. Hmm, pretty strange...

I also wonder how I can make MC to calculate an average date value from my new field when sorting on for example Artist - Album. Now it just shows [Varies], see image enclosed.

[MrC]

MC automatically calculates some average values when a field's value varies and the field is displayed in an area where only one value can be displayed (a cell, category, caption, thumbnail, etc.).  But it won't do this for calculated fields.  So you'll have to calculate your own average using the technique presented here:

   http://yabb.jriver.com/interact/index.php?topic=77826.0

Use ConvertDate() to obtain an MC date number, add the values across tracks, and use FormatDate() on the result.

[Humbledore]

Thanks!
But I think your referring thread is a little bit too heavy for me right now. I maybe come back to it when I have more time...

[MrC]

If it is important to you, I'll give you the rules to add.

[Humbledore]

If you can do that I would be much grateful! 

[MrC]

Actually, try this.  If you just want to use the field for sorting purposes, you can force the output value to be a number:

If(Compare([Date Imported,0], >, ConvertDate(2013-04-01,0)), [Date Imported], [Date Created])&datatype=[number]

[Humbledore]

Now the date (visual) format is reduced to just the year, see enclosed image. Perhaps a consequence of the change...?
But if the sorting is correctly performed 'behind the scenes' so to speak (considering the whole date value), and there is no relative simple solution to display the whole value I can live with this solution.
Thanks a lot!  :-)

[MrC]

I'm confused by your report that you see a Year only value. The output should be an MC-internal value representing the number of days since its date epoch, not a year.

Can you show your expression?

[Humbledore]

See enclosed image.
Thanks!

[MrC]

The calculated field's expression output needs to match the calculated field's type correctly.  In this case, the expression is outputting a formatted date/time string - probably not what we want.

So, let's step back.

You're trying to create and use a Date type field, so your field [Date Conditional Imported] should be set to a Date type.  Change it, save it, and then re-open the field for edit.  Now set the Calculated data Expression to:

   if(compare([Date Imported,0], >, ConvertDate(2013-04-01)), [Date Imported,0], [Date Created,0])

Note how ,0 is added to both the possible date fields that may be output.  Thus, the expression always outputs an MC internal date value.

Once done, the field will show as a formatted date by default in cells, or you can use [Date Conditional Imported,0] as a raw decimal value in comparisons.

[Humbledore]

Thanks a lot for your effort to trying to fix this!

Maybe I don't understand completely what is needed to make this work, but I think this is not what you expected... (see the image enclosed that shows the Artist - Album details view). Neither a formatted date, nor a calculated average value.
But I did what you suggested; I saved the field as a Date Data Type, opened it again and put your expression into the calculated field.

Did I miss something essential here?

 

[vagskal]

I have not followed this thread at close, but my guess is that your locale has comma as the decimal point while MC internally uses a dot and you might therefore be comparing apples with pears.

Please see if this from the wiki page on the expression language (http://wiki.jriver.com/index.php/Media_Center_expression_language) helps:

Please also note: The Math() function uses dot (.) as the decimal point, so Math(1,5+1,5) will not work but Math(1.5+1.5) will. If you run MC on a system that uses comma (,) as the decimal point, all date related fields - [Date,0], [Last Played,0], [Date Modified,0], [Date Imported,0] etc. - with time information will in their raw format use comma as the decimal point. This is also the case with the Now() function. In these cases you must replace the commas with dots for the Math() function to work, like this: Math(Replace(Now(),/,,.) - Replace([Last Played,0],/,,.)). The escape character / must be used to have the commas recognised as commas.

[MrC]

Adding to vagskal's important point, which I noticed also last night, but wanted to get a solid answer first before wasting more of your time, as I think I've been sending you on a wild goose chase due to my own lack of understanding.

MC does treat calculated expressions differently when it comes to aggregate values.  Testing shows that using sometimes the value [Varies] will be output, and at other times only a single value from the set will be output (perhaps the first value seen?).  I can't give a clear explanation yet of what triggers one output or the other, so we'll ignore that.  The salient point now is that we're back to having to use calculations as referenced in the previously mentioned thread.

Here are the rules that must be applied for Set Rules For File Display for the view:

[=save(0,v_dci_[album]_[album artist (auto)])save(0,v_ntracks_[album]_[album artist (auto)])1]=1 [=save(math([Date Conditional Imported,0] + load(v_dci_[album]_[album artist (auto)])), v_dci_[album]_[album artist (auto)])save(math(1 + load(v_ntracks_[album]_[album artist (auto)])), v_ntracks_[album]_[album artist (auto)])1]=1

Here is a rule that can be used in a column to show the average of the calculated imported date:

formatdate(math(load(v_dci_[album]_[album artist (auto)]) / load(v_ntracks_[album]_[album artist (auto)])), Date Time avg)

These rules have not taken into account the locale differences vagskal mentions.  Perhaps someone can supply the required modifications necessary to the above rules.

[vagskal]

Adding to vagskal's important point, which I noticed also last night, but wanted to get a solid answer first before wasting more of your time, as I think I've been sending you on a wild goose chase due to my own lack of understanding.

MC does treat calculated expressions differently when it comes to aggregate values.  Testing shows that using sometimes the value [Varies] will be output, and at other times only a single value from the set will be output (perhaps the first value seen?).  I can't give a clear explanation yet of what triggers one output or the other, so we'll ignore that.  The salient point now is that we're back to having to use calculations as referenced in the previously mentioned thread.

Here are the rules that must be applied for Set Rules For File Display for the view:

[=save(0,v_dci_[album]_[album artist (auto)])save(0,v_ntracks_[album]_[album artist (auto)])1]=1 [=save(math([Date Conditional Imported,0] + load(v_dci_[album]_[album artist (auto)])), v_dci_[album]_[album artist (auto)])save(math(1 + load(v_ntracks_[album]_[album artist (auto)])), v_ntracks_[album]_[album artist (auto)])1]=1

Here is a rule that can be used in a column to show the average of the calculated imported date:

formatdate(math(load(v_dci_[album]_[album artist (auto)]) / load(v_ntracks_[album]_[album artist (auto)])), Date Time avg)

These rules have not taken into account the locale differences vagskal mentions.  Perhaps someone can supply the required modifications necessary to the above rules.

The [Date Conditional Imported,0] field might need to be written like this Replace([Date Conditional Imported,0],/,,.) since it is inside a Math() function. The Compare() thing in the [Date Conditional Imported] field expression seems to be working without Replace() though.

I find that ISO dates sorts well so I do not think there will be a need to Dateconvert() them for sorting purposes.