Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

YTD vs Last YTD Comparison

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!

1 Solution

Accepted Solutions
rubenmarin

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)

View solution in original post

11 Replies
OmarBenSalem

Please refer to this thread :

YTD, MTD issue

Anonymous
Not applicable
Author

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...

kumarkp412
Creator II
Creator II

Hi Guy,

Can you please share the expression of prior comparison calculation.

Thanks

Kumar KVP

rubenmarin

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.

Anonymous
Not applicable
Author

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!

rubenmarin

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.

Anonymous
Not applicable
Author

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?

rubenmarin

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?



Anonymous
Not applicable
Author

%Datum_Key is a nummeric version like 41604, 42058