Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
caccio88
Creator II
Creator II

Calculate the same last year period

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

1 Solution

Accepted Solutions
sunny_talwar

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')) & '<=' &

$(='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

View solution in original post

10 Replies
Clever_Anjos
Employee
Employee

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

The As-Of Table

sunny_talwar

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

caccio88
Creator II
Creator II
Author

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

sunny_talwar

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 .

sunny_talwar

Basically this

='>=' & 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) & ')')

in a text box object gives you the range you needed

Capture.PNG

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

$(='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) & ')')

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

Does this help explain what the expression is doing?

caccio88
Creator II
Creator II
Author

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

caccio88
Creator II
Creator II
Author

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?

sunny_talwar

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?

sunny_talwar

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')) & '<=' &

$(='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