Print

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

[arcspin]

Ok, I have had some more time pondering on how to make my Theater view more informative and have this request for an expression.

I would like to show an icon when a movie has its yearly anniversary, like this:
If today (month and day) is equal to Library field "date (release)", show <img src="tooltip:Anniversary">

and a bonus question:
A way of sorting movies in order of today's day, so movies that have an anniversary on this day will be showed first and then future anniversary's in descending order from today.
(That way I could choose a move to watch in regards of its anniversary date)


This is how the expression looks like to show how old a movie is in years:

Code: [Select]

Math(int((Left(Now(),5)-[Date (Release),0])/365.25))


Best regards,

//arcspin

[wer]

Take a look at the If() function, and the IsEqual() function.
https://wiki.jriver.com/index.php/Conditional_Functions#If
https://wiki.jriver.com/index.php/Test_and_Comparison_Functions#IsEqual

IsEqual lets you compare two values.
If lets you display one thing if the test (you would use IsEqual as your test) is true, and another thing if the test is false.  In your case, the false expression would be empty space, nothing.

You can use the Now() expression to get the current date, which you would compare with a library field using IsEqual.

Give it a try...

Your "bonus" question would require some complicated expressions and the creation of a calculated field, as well as the creation of a sort preset. You should research the ConvertDate() and Math() functions if you really want to attempt this.  I presume you know how to create calculated fields and sort presets.

[marko]

Bear in mind that now() returns the RAW, non formatted value for "now", so, probably best to format  both now(), and the library field the same way using formatdate(), then do the compare... Something along the lines of...
If(isequal(formatdate([field,0],yyyy//MM//dd),formatdate(now(),yyyy//MM//dd),1),yes ,no)

This doesn't get you there, but should be enough to start you rolling.

[arcspin]

Thank you for your advise,
The sorting idea seems to be too complicated, I will try to manage the anniversary image instead.

Stay safe,

//arcspin

[wer]

The wiki entry for Now() gives an example of how to use FormatDate and how to perform Math on a date. If you try to copy/paste Marko's example you'll get an error; I think he intended it as just a guide.

[arcspin]

Understood, I will look into the wiki.

[wer]

Thank you for your advise,
The sorting idea seems to be too complicated, I will try to manage the anniversary image instead.

No problem, glad to help.  So that you are not discouraged in the future, the bonus one CAN be done, but it is not trivial.  The first one you'll find is quite straightforward. 

You could make the anniversary one a bit more complicated if you wanted it to display just on the 5th/10th/20th anniversary instead of every year, but that can be accomplished with a little more Math and/or the IfCase statement.

[lepa]

Anniversary as a day is little problematic for me because of leap years. I mean that does movie released on 29.2 only have anniversary day only ~every 4 years? In that sense it might be better use anniversary years (5th,10th,2th etc)

[RoderickGI]

I mean that does movie released on 29.2 only have anniversary day only ~every 4 years?

Yes. That's what makes them special. 

[zybex]

Challenge accepted 

To check if today is a birthday we just need to check if the day and month are the same. This avoids any problems with leap years as we don't even need to look at the year. So this is enough:

Code: [Select]

if(isequal(formatdate([Date (release),0],dd//MM), formatdate(now(),dd//MM)), Cake!, no cake)
Just replace "cake!" with a picture to have it displayed on the anniversary day - this displays "cake.png":

Code: [Select]

if(isequal(formatdate([Date (release),0],dd//MM), formatdate(now(),dd//MM)), <img src="tooltip:cake">,)
The "no cake" part is where we want to display the number of days until next anniversary. This is much more complicated as it needs to take into account leap years, and also whether the next anniversary is this year (still to come) or next year (already passed this year). Here's an expression that shows the number of days until next anniversary, or "cake!" on the release day - you can put this on a Field and use it for sorting:

Code: [Select]

if(isequal(formatdate([Date (release),0], dd//MM), formatdate(now(), dd//MM)),
    Cake!,
    if(isequal(now(), ConvertDate(formatdate([Date (release),0], dd//MM)//formatdate(now(), yyyy), dd//MM//yyyy), 3),
        Math(int(ConvertDate(formatdate([Date (release),0], dd//MM)//formatdate(now(), yyyy), dd//MM//yyyy) - now()) + 1) days,
        Math(int(ConvertDate(formatdate([Date (release),0], dd//MM)//formatdate(Math(now()+365.25), yyyy), dd//MM//yyyy) - now() + 1)) days))

The language lacks functions to easily do date math, which explains why the expression is so convoluted. In english, it does this:
- compares the day and month of [Date Release] with now() (today's date), ignoring the year
- if they match, show the cake
- else: checks if this year's anniversary is in the past or future (using isEqual mode 3 to compare the dates as numbers)
-- if it's in the past, calculate the number of days as [next year's anniversary] minus [today]
-- if it's in the future, calculate the number of days as [this year's anniversary] minus [today]

Breakdown of the expression components:
A = [Date (release),0]  -> release date as a number (number of days since 1900)
B = formatdate(now(), yyyy)  -> current year (2020)
C = formatdate(Math(now()+365.25), yyyy) -> next year (2021 - yeah, might misbehave around december 31 on leap years...)
D = formatdate(A, dd//MM)  -> release date without year (13/08)
E = D//B -> this year's anniversary (day/month of release + current year)
F = ConvertDate(E, dd//MM//yyyy)  -> convert it back to a number
days = Math(int(F - now()) + 1)  -> number of days = NextAnniversary - today + 1 (the +1 is needed due to day fractions)

Matt: how about some new Date Math functions?

[arcspin]

AWESOME 

Both expressions work and I do like them both.
There is now a birthday cake icon on the the release date just in front of the title of the movie 

I do not quite understand the -days that are shown in your last expression?
The regular number of days until the anniversary I do get.

[zybex]

Did you copy the full expression without changes? It should not display any negative number, it's always a positive number of days until next anniversary (or "cake").
Here's a screenshot from my own collection:
 

[arcspin]

Yes.

This is the expression that I use:

Code: [Select]

if(isequal(formatdate([Date (release),0], dd//MM), formatdate(now(), dd//MM)),
    Cake!,
    if(isequal(now(), ConvertDate(formatdate([Date (release),0], dd//MM)//formatdate(now(), yyyy), dd//MM//yyyy), 3),
        Math(int(ConvertDate(formatdate([Date (release),0], dd//MM)//formatdate(now(), yyyy), dd//MM//yyyy) - now()) + 1) days,
        Math(int(ConvertDate(formatdate([Date (release),0], dd//MM)//formatdate(Math(now()+365.25), yyyy), dd//MM//yyyy) - now() + 1)) days))

I also tried to change the date format as that have been a problem earlier:

Code: [Select]

if(isequal(formatdate([Date (release),0], MM//dd), formatdate(now(), MM//dd)),
    Cake!,
    if(isequal(now(), ConvertDate(formatdate([Date (release),0], MM//dd)//formatdate(now(), yyyy), yyyy//MM//dd), 3),
        Math(int(ConvertDate(formatdate([Date (release),0], MM//dd)//formatdate(now(), yyyy), yyyy//MM//dd) - now()) + 1) days,
        Math(int(ConvertDate(formatdate([Date (release),0], MM//dd)//formatdate(Math(now()+365.25), yyyy), yyyy//MM//dd) - now() + 1)) days))and that didn't work.


https://yabb.jriver.com/interact/index.php/topic,119385.msg870111.html#msg870111


https://yabb.jriver.com/interact/index.php/topic,119385.msg870202.html#msg870202

[zybex]

Can you add your [Date (release)] column to the screenshot?
Date format should not matter with this expression.

[arcspin]

Yes

This post might help
https://yabb.jriver.com/interact/index.php/topic,119385.msg870202.html#msg870202

[zybex]

OK. Can you please add another expression column to the screenshot with "[Date (release),0]" so that we can see them side by side?

I think the problem is that MC is not recognizing that date format as a valid date, and is returning "0" for most dates instead of the correct number equivalent. This may happen if the date format you have on that field (yyyy-MM-dd) is different from your Windows regional settings.

This would also explain the issues you had with other date formulas.

[arcspin]

Ok,
I have Swedish language in my Win 10 OS and English language in JRiver.

That could be a problem !?

Note: My windows date format should be yyyy-mm-dd

[arcspin]

My settings in WIN 10 OS

[arcspin]

JRiver settings

[zybex]

All looks fine... the Date(release) values are OK.
What version of JRiver do you have? (full version number)
It looks like the "isEqual() function is not behaving the same for you and me. Can you try with mode 5 instead?

if(isequal(formatdate([Date (release),0], dd//MM), formatdate(now(), dd//MM)),
    Cake!,
    if(isequal(now(), ConvertDate(formatdate([Date (release),0], dd//MM)//formatdate(now(), yyyy), dd//MM//yyyy), 5),
        Math(int(ConvertDate(formatdate([Date (release),0], dd//MM)//formatdate(now(), yyyy), dd//MM//yyyy) - now()) + 1) days,
        Math(int(ConvertDate(formatdate([Date (release),0], dd//MM)//formatdate(Math(now()+365.25), yyyy), dd//MM//yyyy) - now() + 1)) days))

[arcspin]

26.0.106 (64-bit)

[arcspin]

Ok, I changed the system settings to US date format and it looks much better now.
It is how ever not a long term solution   


 

[zybex]

Hmm. :-/
Alright, another possibility is the dot/comma decimal separator. Please post the result of these formulas using ZELDA:

now()
math(now())
math(int(now()))
math(int(now() + 365.25))

Output should be similar to this (first 2 values should differ slightly):
44056.9184027777810116
44056.918403
44056
44422

[arcspin]

Output is

[zybex]

That's unreadable, please post a full resolution screenshot.

[arcspin]

Bigger

[arcspin]

 updated Zelda and here are correct values.

[arcspin]

Did you note that when I changed to US date format the expression worked.

FYI: I'm on yyyy-mm-dd when I did the Zelda screen shots

[zybex]

It didn't work, the values are wrong for anniversary dates lower than current date (06/03 until today is not 205 days - regardless of 03/06 = june 3rd or March 6th). There's something weird going on on your PC, not sure if it's a bug in MC or some combination of regional settings.
Nevermind - it is 205 days from today until 03/06/2021. So it's really related to MC not recognizing your date values in the [Date (release)] field :-/

Please run this in Zelda and send the output - pick a Movie where the result is negative in the first line of output (please restore your normal date settings)

Code: [Select]

if(isequal(formatdate([Date (release),0], dd//MM), formatdate(now(), dd//MM)),
    Cake!,
    if(isequal(now(), ConvertDate(formatdate([Date (release),0], dd//MM)//formatdate(now(), yyyy), dd//MM//yyyy), 3),
        Math(int(ConvertDate(formatdate([Date (release),0], dd//MM)//formatdate(now(), yyyy), dd//MM//yyyy) - now()) + 1) days,
        Math(int(ConvertDate(formatdate([Date (release),0], dd//MM)//formatdate(Math(now()+365.25), yyyy), dd//MM//yyyy) - now() + 1)) days))

[Date (release)]
[Date (release),0]
formatdate([Date (release),0], dd//MM)//formatdate(now(), yyyy)
formatdate(Math(now()+365.25), yyyy)
ConvertDate(formatdate([Date (release),0], dd//MM)//formatdate(now(), yyyy), dd//MM//yyyy)
ConvertDate(formatdate([Date (release),0], dd//MM)//formatdate(Math(now()+365.25), yyyy), dd//MM//yyyy)
case = isequal(now(), ConvertDate(formatdate([Date (release),0], dd//MM)//formatdate(now(), yyyy), dd//MM//yyyy), 3)
case 0 : Math(int(ConvertDate(formatdate([Date (release),0], dd//MM)//formatdate(now(), yyyy), dd//MM//yyyy) - now()) + 1) days
case 1 : Math(int(ConvertDate(formatdate([Date (release),0], dd//MM)//formatdate(Math(now()+365.25), yyyy), dd//MM//yyyy) - now() + 1)) days

[arcspin]

Yes, I'm on normal settings.

[zybex]

We're getting somewhere. On your system, this returns blank when it should return 2021 (next year):
formatdate(Math(now()+365.25), yyyy)

Perhaps you have a comma as decimal separator in Windows instead of dot. Please try these to check which ones return 2021:
a=formatdate(Math(now()+365.25), yyyy)
b=formatdate(Math(now()+365/,25), yyyy)
c=formatdate(Math(now()+365), yyyy)
d=formatdate(Math(int(now()+365.25)), yyyy)
e=formatdate(Math(int(now()+365/,25)), yyyy)
f=formatdate(Math(int(now())+365), yyyy)

On my system the only one that fails is B.
You can try simply changing the original expression to use "365" instead of "365.25". If it works, you'll only see bad results on Jan 1st on a leap year

[lepa]

formatdate(Math(now()+365.25), yyyy)
Swedish localizations needs those Replace() functions to change commas to periods when using Math()

Now() returns decimal number so calcution doesn't work
e.g.
formatdate(Math(123123,12312 + 365.25), yyyy)

[arcspin]

d=2020
e=2020
f=2020


a,b,c=nothing

[arcspin]

You can try simply changing the original expression to use "365" instead of "365.25".

It didn't work, the output is the same

Code: [Select]

if(isequal(formatdate([Date (release),0], dd//MM), formatdate(now(), dd//MM)),
    Cake!,
    if(isequal(now(), ConvertDate(formatdate([Date (release),0], dd//MM)//formatdate(now(), yyyy), dd//MM//yyyy), 3),
        Math(int(ConvertDate(formatdate([Date (release),0], dd//MM)//formatdate(now(), yyyy), dd//MM//yyyy) - now()) + 1) days,
        Math(int(ConvertDate(formatdate([Date (release),0], dd//MM)//formatdate(Math(now()+365), yyyy), dd//MM//yyyy) - now() + 1)) days))

[zybex]

formatdate(Math(now()+365.25), yyyy)
Swedish localizations needs those Replace functions to change commas to periods

But now() is returning 44056.918402 for him, so everything already has periods.
Or does Math require commas when the regional settings are set to comma?

Arcspin, 2020? Not 2021? WTF.

[arcspin]

Yes,

[lepa]

But now() is returning 44056.918402 for him, so everything already has periods.
Or does Math require commas when the regional settings are set to comma?

Arcspin, 2020? Not 2021? WTF.

Now() returns decimal number and comma is a decimal separator when using Swedish settings. Probably was using US layout when it returned number with period.

Code: [Select]

formatdate(Math(Replace(now()+365.25,/,,.)), yyyy)should work for everyone

[arcspin]

I have change from , to . in WINDOWS.
and it now works.

[arcspin]

Zelda now outputs 2021

[arcspin]

Probably was using US layout when it returned number with period.

No, Every response from me to Zybex have been in Swedish yyyy-mm-dd and ,

[zybex]

Alright, let's just get rid of all decimal math. Here's a cleaned up expression using only integers. The save() calls are just a way to avoid duplicating code, makes it more readable:

Code: [Select]

save(left(now(),5), today)/
save(formatdate([today], yyyy), thisYear)/
save(formatdate([Date (release),0], dd//MM), releaseDay)/
if(isequal([releaseDay], formatdate([today], dd//MM)),
    Cake!,
    if(isequal([today], ConvertDate([releaseDay]//[thisYear], dd//MM//yyyy), 3),
        Math(ConvertDate([releaseDay]//[thisYear], dd//MM//yyyy) - [today]) days,
        Math(ConvertDate([releaseDay]//Math([thisYear]+1), dd//MM//yyyy) - [today]) days))
With this one it shouldn't matter which date/number formats you're using.

[arcspin]

YES, it works.
FYI, I'm back on ","

[arcspin]

and Cake!

[lepa]

Alright, let's just get rid of all decimal math. Here's a cleaned up expression using only integers. The save() calls are just a way to avoid duplicating code, makes it more readable:

Code: [Select]

save(left(now(),5), today)/
save(formatdate([today], yyyy), thisYear)/
save(formatdate([Date (release),0], dd//MM), releaseDay)/
if(isequal([releaseDay], formatdate([today], dd//MM)),
    Cake!,
    if(isequal([today], ConvertDate([releaseDay]//[thisYear], dd//MM//yyyy), 3),
        Math(ConvertDate([releaseDay]//[thisYear], dd//MM//yyyy) - [today]) days,
        Math(ConvertDate([releaseDay]//Math([thisYear]+1), dd//MM//yyyy) - [today]) days))
With this one it shouldn't matter which date/number formats you're using.

Cool, I didn't know that varibles worked without load. That knowledge will clean up nicely some of my expressions! 

[arcspin]

Wow,
This is amazing!
Sorry to have caused you headache because my date format is different.

Glad to have you back and thank you for these expressions !!!!

//arcspin

[zybex]

It took 35+ posts, but we got there 

[marko]

Alright, let's just get rid of all decimal math. Here's a cleaned up expression using only integers. The save() calls are just a way to avoid duplicating code, makes it more readable:

Code: [Select]

save(left(now(),5), today)/
save(formatdate([today], yyyy), thisYear)/
save(formatdate([Date (release),0], dd//MM), releaseDay)/
if(isequal([releaseDay], formatdate([today], dd//MM)),
    Cake!,
    if(isequal([today], ConvertDate([releaseDay]//[thisYear], dd//MM//yyyy), 3),
        Math(ConvertDate([releaseDay]//[thisYear], dd//MM//yyyy) - [today]) days,
        Math(ConvertDate([releaseDay]//Math([thisYear]+1), dd//MM//yyyy) - [today]) days))
With this one it shouldn't matter which date/number formats you're using.

Cool, I didn't know that varibles worked without load. That knowledge will clean up nicely some of my expressions! 

Cool indeed. I was not aware of this either. Thanks Zybex.
Are there any upsides/downsides to taking this approach? Wondering if it could be used to speed these up...
https://yabb.jriver.com/interact/index.php/topic,102319.msg709847.html#msg709847

-marko

[zybex]

The upside for me is that it makes complex expressions more readable. I hate duplicating code and this method avoids that.
It is also a bit faster. Using ZELDA to check the execution times of the above expression on the Playlist tab, I see:
- expression without Saved variables (lots of repeated code): execution times from 1.4 to 2.0ms
- expression with Saved variables: execution times from 0.9 to 1.4ms

So that's a ~50% improvement, not bad when running complex expressions on thousands of files to filter them. Of course, YMMV, the actual improvement depends on the complexity of the expression.

Downsides, none that I can see. These variables are only saved on the current execution context (for the current file being evaluated), so as long as MC cleans up memory allocation afterwards, there should be no downside.

Your expressions for lastWeek/currWeek could benefit from this, of course... but I think they can be much optimized. Here are equivalent expressions (I think):

Current week - starting day is monday Sunday):

Code: [Select]

[=isEqual(left([date imported,0],5), Math(left(now(),5) - formatdate(left(now(),5),%w)), 6)]=1
Previous Week:

Code: [Select]

[=and(isEqual(left([date imported,0],5),Math(left(now(),5)-formatdate(left(now(),5),%w)-7),6),isEqual(left([date imported,0],5),Math(left(now(),5)-formatdate(left(now(),5),%w)),3))]=1
Or, refactored with save():

Code: [Select]

[=save(left(now(),5), today)/
save(left([date imported,0],5), imported)/
save(Math([today] - formatdate([today],%w)), currWeekStart)/
save(Math([currWeekStart] - 7), prevWeekStart)/
and(isEqual([imported], [prevWeekStart], 6), isEqual([imported], [currWeekStart], 3))]=1
Much more readable
Not necessarily shorter/faster though, in this case.

[marko]

Interesting. And() was not available when these were conceived, and when they make your head hurt, it's often easier to just be happy that they're working rather than revisit for optimising!

I don't have a lot of time to play this weekend, but, switching the expressions to use [last played] as there's more results there, "Last Week" (not looked at "This Week") is missing Sunday's data...

Code: [Select]

[=and(isEqual(left([last played,0],5),Math(left(now(),5)-formatdate(left(now(),5),%w)-7),6),isEqual(left([last played,0],5),Math(left(now(),5)-formatdate(left(now(),5),%w)),3))]=1
Also, "week number" for the week that spans across December 31st and January 1st gave me problems as week 53 and week 0 were the same week. My unoptimised expression deals with that, but only by adding repitition and extra levels (=yuk)

Curious to see if your optimised examples have this covered via a simpler route, but, not this weekend. Will try and make time to play towards the tail end of next week

[zybex]

and(A,B) = if(A,if(B,1,0),0) ... but I agree, if it ain't broken, don't fix it. However in this case I was trying to understand your expression and I found it so convoluted that I just decided to rewrite it.

Regarding Sunday - actually the starting day for %w is Sunday, not Monday as I said above. So the above expressions are returning Sunday-to-Saturday. To adjust for Monday-to-Sunday we need to tweak the calculation a bit, like so:

This Week, starting on Monday:

Code: [Select]

[=isEqual(left([last played,0],5), Math(left(now(),5) - (formatdate(left(now(),5),%w)+6)%7), 6)]=1
Previous Week, starting on Monday:

Code: [Select]

[=save(left(now(),5), today)/
save(left([last played,0],5), played)/
save(Math([today] - (formatdate([today],%w)+6)%7), currWeekStart)/
save(Math([currWeekStart] - 7), prevWeekStart)/
and(isEqual([played], [prevWeekStart], 6), isEqual([played], [currWeekStart], 3))]=1
Only change is in currWeekStart, see if you can figure out what's it doing

These expressions work on any week, including rollover on week 52/53-00. They just deal with the day number (today is 44058), calculate the week start day using %w (currWeekStart), and then check if [prevWeekStart] <= [Last Played] < [CurrWeekStart].