Current Year vs Previous Year Sort Order

[#1]

Hi All,

I've Googled endlessly but seem unable to find a solution to my issue.

I have a table which holds records with a date field.  I am using the QlikView Components qvs to generate my "master calendar" from the date field (CaseDate).

The user can select Year, Month and Day to filter the various charts/tables on the report.

However, I want one chart to show a sum of figures based on the max date the person selects.  The chart should show the 12 months leading up to the selected date as the CY and the 12 months prior to that as the PY.

For example, if the user selects Year=2017, Month=Feb then I want the following data:

Current Year = Total records Mar-2016 to Feb-2017

Previous Year = Total records Mar-2015 to Feb-2016

Now, I can get this information using a Set Analysis but if I just return the month name then I can't get it to sort with Mar first and Feb last.  If I return the year to sort then I get 24 records rather than 12:

CaseYear-Month	CY	PY
2015-Mar	0	210
2015-Apr	0	171
2015-May	0	189
2015-Jun	0	244
2015-Jul	0	228
2015-Aug	0	169
2015-Sep	0	224
2015-Oct	0	224
2015-Nov	0	222
2015-Dec	0	183
2016-Jan	0	210
2016-Feb	0	243
2016-Mar	213	0
2016-Apr	204	0
2016-May	202	0
2016-Jun	212	0
2016-Jul	218	0
2016-Aug	196	0
2016-Sep	223	0
2016-Oct	197	0
2016-Nov	226	0
2016-Dec	174	0
2017-Jan	216	0
2017-Feb	207	0

I've attached a screenshot to try and show the issue.

Can anyone point me in the right direction?

Many thanks,

Phil

[adamdavi3s]

Inspired sir!!

[Frank_Hartmann]

but without your piece of code i would try untill tomorrow.

i recently had a similar problem and swuehl gave me the final advice with mod function.

Just had to transform his solution to the actual problem 🙂

[sunny_talwar]

This is what I love about this community, we work together to get better solutions and help each other learn while helping others

[#5]

What an amazing community!  My first post and I never expected such swift and helpful responses.

=Mod(Only({1}MonthName)-Min(TOTAL MonthName+1),12)+1 worked perfectly for me.  Now to research in to how it works to understand it better

Thanks again to you all!