topic Re: Calculate the same last year period in QlikView

Calculate the same last year period

caccio88 — Thu, 08 Sep 2016 17:48:06 GMT

Hi everyone,

what i want is to compare year to date period with the same last year period. The max date of my data is July 2016 and the year starts in March 2016. I've already created my new fiscal year from March to February but I can't do the right calculation of the last year in the same period.

At the end I want the value from March 2015 to July 2015.

I tried reading this Year-over-Year Comparisons, but wasn't so helpful for me.

Here attached my example.

stalwar1 swuehl gwassenaar cleveranjos

Re: Calculate the same last year period

Clever_Anjos — Thu, 08 Sep 2016 17:53:32 GMT

I think the best solution would be using a As-Of table with proper flag to what you need

The As-Of Table

Re: Calculate the same last year period

sunny_talwar — Thu, 08 Sep 2016 19:05:22 GMT

May be try this:

Sum({<DataMonthYear ={"$(='>=' & Date(YearStart(Max(DataMonthYear),-1, 3), 'MMM-YYYY') & '<=' &

$(='Date(AddYears(Max({<DataMonthYear = {' & Chr(34) & '=Sum({<CATEGORY={' & Chr(39) & 'ACTUAL' & Chr(39) & '}, ACCOUNT={' & Chr(39) & 'CFFCF' & Chr(39) & '}>}Measures2)' & Chr(34) & '}>}DataMonthYear), -1), ' & Chr(39) & 'MMM-YYYY' & Chr(39) & ')'))"}, CATEGORY={'ACTUAL'},ACCOUNT={'CFFCF'}>} Measures2)/1000000

Re: Calculate the same last year period

caccio88 — Thu, 08 Sep 2016 20:06:48 GMT

The results is perfect! Thank you Sunny! stalwar1‌ But I can't clearly understand how it works...

Re: Calculate the same last year period

sunny_talwar — Thu, 08 Sep 2016 20:08:19 GMT

I had to go through a painful process of making it to work . I am outside for a little bit, but can brief you on how its working once I am near my computer .

Re: Calculate the same last year period

sunny_talwar — Thu, 08 Sep 2016 21:07:35 GMT

Basically this

='>=' & Date(YearStart(Max(DataMonthYear),-1, 3), 'MMM-YYYY') & '<=' &

in a text box object gives you the range you needed

The Mar-2015 was easy to get

Date(YearStart(Max(DataMonthYear),-1, 3), 'MMM-YYYY')

But July-2016 was difficult because the max date was Feb-2017, but for this particular expression Sum({<CATEGORY={'ACTUAL'},ACCOUNT={'CFFCF'}>} Measures2) the max date was July-2016. To get this, I had to use a set analysis, but doing a double quote within a double quote is an issue, so I had to use a dollar sign expansion to get this to work

The above gives me July-2015 because I use AddYears(July-2016, -1)

Does this help explain what the expression is doing?

Re: Calculate the same last year period

caccio88 — Thu, 08 Sep 2016 21:21:12 GMT

Wonderful explanation! the best as usual! thank you Sunny! stalwar1‌

Re: Calculate the same last year period

caccio88 — Fri, 09 Sep 2016 06:18:32 GMT

stalwar1‌ tha solution works without other selection but if I select on DataMonthYear like Jul-16 the set doesn't works.... so with the selection mentioned before we don't have -1,68 ( the value of Jul-15) but always -61,70.

Any idea for this problem?

Re: Calculate the same last year period

sunny_talwar — Fri, 09 Sep 2016 07:12:57 GMT

Isn't the idea of YTD to show always show a YTD number even if you select a single MonthYear? When you select Jul-2016, showing Jul-2016's number would defeat the purpose, no? I see that your current year's expression is doing that and we might be able to configure your other expression to do that as well, but currently your last year's expression is behaving like a true YTD expression which does change when you select Jun-2016 by summing up the number from Mar-2016 till Jun-2016. Is this not what you would want and see just one month's number on selection of a month?

Re: Calculate the same last year period

sunny_talwar — Fri, 09 Sep 2016 07:21:46 GMT

Here is the expression which will meet your requirement of selection of a particular month

Sum({<DataMonthYear = {"$(='>=' & If(GetSelectedCount(DataMonthYear) >= 1, Date(AddYears(Min(DataMonthYear), -1), 'MMM-YYYY'), Date(YearStart(Max(DataMonthYear),-1, 3), 'MMM-YYYY')) & '<=' &

CATEGORY={'ACTUAL'},ACCOUNT={'CFFCF'}>} Measures2)/1000000

Re: Calculate the same last year period

caccio88 — Fri, 09 Sep 2016 07:42:03 GMT

This is exactly what I was looking for! thanks again