Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
Could someone help me out a bit. I've already used the advice from topic: Year to Date vs Year to date Last Year
But for some reason my calculation doesnt works for the prioryear.
I added an image where you can see the variables and calculations.
Thnx!
The date value is fixed, doesn't matter the selections, if manually changing the number works it means than the number format works and you can change to the expected format:
vPriorYearDate =Num(addyears(max(%datum_Key), -1))
The expression should work, if not try with double quotes:
=SUM({<calendar.Year={'$(vPriorYear)'},calendar.Month=,%datum_Key={"<=$(vPriorYearDate)"},calendar.YearMonth=>}omzet.Linetot)
Please refer to this thread :
Hello Omar,
Thanks for your reply. But if you look at the attached image, you can see thats the prior comparison give a problem en results in € 0,-.
So there's no problem to calculate YTD or MTD...
Hi Guy,
Can you please share the expression of prior comparison calculation.
Thanks
Kumar KVP
Hi Guy, the date format of vMaxDate and vPriorYearDate are different, try setting the same format, or changing the format to number.
Also, when doing comparisons in set analysis values (like: <=$(vMaxDate)) is better (or necesary) use double quotes instead of simple quotes.
As a tip, to show what is doing the $-expansion you can remove the caption of the expression, then hovering the mouse over the caption you can check how are converted vMaxDate and vPriorYearDate.
Hello Kumar,
hereby my variables and calculations:
vMaxDate =max(%datum_Key)
vMaxDay =day(max(%datum_Key))
vMaxMonth =month(max(%datum_Key))
vMaxYear =max(calendar.Year)
vMaxYearMonth =vMaxYear&'-'&vMaxMonth
vPriorMonth =month(addmonths(max(%datum_Key) ,- 1))
vPriorMonthYear =Year(addmonths(max(%datum_Key) ,- 1))
vPriorYear =vMaxYear -1
vPriorYearDate =date(addyears(max(%datum_Key), -1), 'DD MMM YYYY')
vMaxDate1 =date(addyears(max(%datum_Key), -0), 'DD MMM YYYY')
Calculation YTD
=SUM({<calendar.Year={'$(vMaxYear)'},[calendar.Inv date]={'<=$(vMaxDate)'},calendar.Month=,calendar.YearMonth=>}omzet.Linetot)
Calcation LYTD
=SUM({<calendar.Year={'$(vPriorYear)'},calendar.Month=,%datum_Key={'<=$(vPriorYearDate)'},calendar.YearMonth=>}omzet.Linetot)
Can you help me or for the calculation LYTD?
Many thanks!
You can try manually setting the date, so you can know wich value and wich format is expecting
In example, checking if this works for prior year:
=SUM({<calendar.Year={'2015'},calendar.Month=,%datum_Key={'<=31-12-2015'},calendar.YearMonth=>}omzet.Linetot)
or:
=SUM({<calendar.Year={'2015'},calendar.Month=,%datum_Key={"<=31-12-2015"},calendar.YearMonth=>}omzet.Linetot)
or:
=SUM({<calendar.Year={'2015'},calendar.Month=,%datum_Key={"<=42369"},calendar.YearMonth=>}omzet.Linetot) // 42369 is 31-12-2015 in numeric format
When you find the correct syntax you can use the tip of checking the expression in the caption (post below) to see if the expanded value is converted as expected.
Hello Rubin,
When im using your method, if get the calculation of the whole year (2015), but when i select a month, the calculation still shows the total amount for the year 2015....
Any idea whats going wrong?
That can be because the expression is ignoring selections in month and the datum filter is not working:
=SUM({<calendar.Year={'$(vPriorYear)'},calendar.Month=,%datum_Key={'<=$(vPriorYearDate)'},calendar.YearMonth=>}omzet.Linetot)
Is %datum_Key a real date or just a string? Have you tried the numeric version?
%Datum_Key is a nummeric version like 41604, 42058