# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
cancel
Showing results for
Did you mean:
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.

1 Solution

Accepted Solutions
MVP

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

10 Replies
Employee

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

The As-Of Table

MVP

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

Creator II
Author

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

MVP

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 .

MVP

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

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?

Creator II
Author

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

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?

MVP

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?

MVP

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