Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

guy_hombleu
New Contributor III

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

Re: YTD vs Last YTD Comparison

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)

11 Replies
omarbensalem
Esteemed Contributor

Re: YTD vs Last YTD Comparison

Please refer to this thread :

YTD, MTD issue

guy_hombleu
New Contributor III

Re: YTD vs Last YTD Comparison

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
Contributor II

Re: YTD vs Last YTD Comparison

Hi Guy,

Can you please share the expression of prior comparison calculation.

Thanks

Kumar KVP

Re: YTD vs Last YTD Comparison

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.

guy_hombleu
New Contributor III

Re: YTD vs Last YTD Comparison

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!

Re: YTD vs Last YTD Comparison

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.

guy_hombleu
New Contributor III

Re: YTD vs Last YTD Comparison

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?

Re: YTD vs Last YTD Comparison

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?



guy_hombleu
New Contributor III

Re: YTD vs Last YTD Comparison

%Datum_Key is a nummeric version like 41604, 42058