Problem with Previous year to date calculation

[#1]

Hi all,

On Qlik Sense, I have to compare YTD actuals Previous Year to Budget previous year.

I have created a Master Calendar. I have created Year, Month, YearMonth fields.

I have formula that does work fine for calculating YTD Actuals and YTD Budget. No problem.

But I do have a problem with Previous YTD for Actuals and Budget.

As long as I don’t have any Year dimension selected, my two formulas are working fine (showing 2015 for Actuals and 2014 for Previous).

But as soon as I select any given Year, then the Previous YTD for Actuals and Budget don’t work anymore, giving 0 as result.

Here are my 2 formulas:

Previous YTD Budget =sum({$<Year_MonthNum={">=$(=max(Year)-1 & '01')  <=$(=max(Year)-1 & num(Month(today()), '00'))"}>} Budget)

Previous YTD Actuals =sum({$<Year_MonthNum ={">=$(=max(Year)-1 & '01')  <=$(=max(Year)-1 & num(Month(today()), '00'))"}, Type_Mission={'Assessment'}>}Invoice)

The same formulas without “-1” (to do current year and not previous) are working just fine, with or without a year selected.

I feel I miss something with set modifier but despite many tries I still don’t understand what is happening (and I’m not a developer). Could someone help me on this?

Thanks in advance

[sunny_talwar · Accepted solution]

Are your trying them in a text box object or a chart?? For text box object you can try this:

Previous YTD Budget =sum({$<Year_MonthNum={">=$(=max(Year)-1 & '01')  <=$(=max(Year)-1 & num(Month(today()), '00'))"}, Year = >} Budget)

Previous YTD Actuals =sum({$<Year_MonthNum ={">=$(=max(Year)-1 & '01')  <=$(=max(Year)-1 & num(Month(today()), '00'))"}, Type_Mission={'Assessment'}, Year = >}Invoice)

I would suggest adding all other calendar fields on which you might make a selection (like Month, Week, Date etc) except Year_MonthNum which is already present.

HTH

Best,

Sunny

[#3]

Hi Sunny!

Hope you are doing well!

Before I got your answer, I found a solution by looking more closely to some posts about "comparison previous year" and especially in post Comparison with the previous year and in Re: Previous year for Pivot Table with Year/Month dimension.

As I suspected, problem was with the set modifier. As per my understanding, the formula was right, but the $ sign at the beginning of the formula was limiting the result to the selected year. And per force, previous year results were excluded of the selected year.

As explained in those posts, by replacing this $ sign by 1, it started to work fine.

The correct formulas are :

Previous YTD Budget =sum({1<Year_MonthNum={">=$(=max(Year)-1 & '01')  <=$(=max(Year)-1 & num(Month(today()), '00'))"}>} Budget)

Previous YTD Actuals =sum({1<Year_MonthNum ={">=$(=max(Year)-1 & '01')  <=$(=max(Year)-1 & num(Month(today()), '00'))"}, Type_Mission={'Assessment'}>}Invoice)

But I tried your solution and it it work as well, as it is causing to ignore the selected year which. But I can't tell if the two solution are absolutely equivalent or not.

Problem solved.

Thanks anyway for your prompt answer!

Best regards,

Patrick

[sunny_talwar]

So the difference between my formula {<Year =>} and your formula {1} is that, in your any selection made within your application won't change the results in the expression, where as what I suggested will make the expression such that it doesn't change based on a selection in Year, but will still change if you make other selections such as country etc.

HTH

Best,

Sunny

[#5]

Hi Sunny,

Thanks for this important information. I will then adjust my formula to use your solution.

Thanks again!

Best,

Patrick

[sunny_talwar]

Not a problem

I am glad I was able to guide you well.

Best,

Sunny