topic Re: Current Year vs Previous Year Sort Order in QlikView

Current Year vs Previous Year Sort Order

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Current Year vs Previous Year Sort Order

adamdavi3s — Tue, 14 Mar 2017 11:04:09 GMT

Hi Phil,

Do you have month number in your master calendar? If not then add it and use a sort on expression

=only({1}MonthNo)

Actually one thing I have never tried is just a sort on expression using

=Num(Month)

That might also work!

Ignore me, that will still put feb first, let me install the components script and see what we have to work with!

Re: Current Year vs Previous Year Sort Order

Tue, 14 Mar 2017 11:47:42 GMT

Thanks Adam.

Yes, this is the problem I'm having. The month number will always put Jan first. As I don't know what the first month is until the user makes a selection, I can't use a pre-populated field.

Re: Current Year vs Previous Year Sort Order

adamdavi3s — Tue, 14 Mar 2017 11:49:12 GMT

Hi Phil,

Can you just clarify what fields you've got in the calendar and what set you're using to return the values.

Re: Current Year vs Previous Year Sort Order

Tue, 14 Mar 2017 12:02:37 GMT

These are the fields in the calendar. It links to the fact table using the CaseDate field.

If I return using the CaseYear_Month then I can sort, but I get 24 records, i.e. the PY and CY. If I return the CaseMonth column (Jan;Feb;Mar;etc,) then it does group into 12 records correctly but I can't sort it.

Many thanks,

Phil

Re: Current Year vs Previous Year Sort Order

adamdavi3s — Wed, 15 Mar 2017 10:22:54 GMT

This is making me so cross, there HAS to be a way

Re: Current Year vs Previous Year Sort Order

Frank_Hartmann — Wed, 15 Mar 2017 11:01:50 GMT

can u prepare a small example qvw to play around.

i guess this way you will get a solution much faster

Re: Current Year vs Previous Year Sort Order

adamdavi3s — Wed, 15 Mar 2017 11:06:16 GMT

This is the one I made to play with (Remember ctrl+q+q) , you're welcome to use it

Re: Current Year vs Previous Year Sort Order

adamdavi3s — Wed, 15 Mar 2017 11:19:04 GMT

Its going to be something insane like

=pick(match(Month,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),Match(only({1}MonthName),'February','March','April','May','June','July','August','September','October','November','December','January'),Match(only({1}MonthName),'March','April','May','June','July','August','September','October','November','December','January','February'),Match(only({1}MonthName),'April','May','June','July','August','September','October','November','December','January','February','March'),Match(only({1}MonthName),'May','June','July','August','September','October','November','December','January','February','March','April'),Match(only({1}MonthName),'June','July','August','September','October','November','December','January','February','March','April','May'),Match(only({1}MonthName),'July','August','September','October','November','December','January','February','March','April','May','June'),Match(only({1}MonthName),'August','September','October','November','December','January','February','March','April','May','June','July'),Match(only({1}MonthName),'September','October','November','December','January','February','March','April','May','June','July','August'),Match(only({1}MonthName),'October','November','December','January','February','March','April','May','June','July','August','September'),Match(only({1}MonthName),'November','December','January','February','March','April','May','June','July','August','September','October'),Match(only({1}MonthName),'December','January','February','March','April','May','June','July','August','September','October','November'),Match(only({1}MonthName),'January','February','March','April','May','June','July','August','September','October','November','December')

)

Re: Current Year vs Previous Year Sort Order

adamdavi3s — Wed, 15 Mar 2017 11:23:12 GMT

stalwar1‌ save me from myself.....

Re: Current Year vs Previous Year Sort Order

adamdavi3s — Wed, 15 Mar 2017 11:28:22 GMT

grrrrr so close

Re: Current Year vs Previous Year Sort Order

sunny_talwar — Wed, 15 Mar 2017 11:30:32 GMT

What are we trying to do here Adam?

Re: Current Year vs Previous Year Sort Order

adamdavi3s — Wed, 15 Mar 2017 11:32:28 GMT

Order the months ending with the selected months basically!

Re: Current Year vs Previous Year Sort Order

adamdavi3s — Wed, 15 Mar 2017 11:37:54 GMT

GOT IT!

I'll tidy this up

Re: Current Year vs Previous Year Sort Order

sunny_talwar — Wed, 15 Mar 2017 11:42:20 GMT

Not sure but may be this

If(Num(Only({1}MonthName))-Min(TOTAL MonthName) >= 0, Num(Only({1}MonthName))-Min(TOTAL MonthName), Num(Only({1}MonthName))-Min(TOTAL MonthName)+12)

If(Num(Only({1}MonthName))-Min(TOTAL MonthName) > 0, Num(Only({1}MonthName))-Min(TOTAL MonthName), Num(Only({1}MonthName))-Min(TOTAL MonthName)+12)

Re: Current Year vs Previous Year Sort Order

adamdavi3s — Wed, 15 Mar 2017 11:44:16 GMT

pahahahahaha so much simpler than mine, I think I went down this route but didn't twig on using TOTAL

This is the correct one I believe, makes sense how that works

If(Num(Only({1}MonthName))-Min(TOTAL MonthName) > 0, Num(Only({1}MonthName))-Min(TOTAL MonthName), Num(Only({1}MonthName))-Min(TOTAL MonthName)+12)

Re: Current Year vs Previous Year Sort Order

sunny_talwar — Wed, 15 Mar 2017 11:50:26 GMT

It can be further simplified by removing Num() which I used for testing purposes

If(Only({1}MonthName)-Min(TOTAL MonthName) > 0, Only({1}MonthName)-Min(TOTAL MonthName), Only({1}MonthName)-Min(TOTAL MonthName)+12)

Re: Current Year vs Previous Year Sort Order

adamdavi3s — Wed, 15 Mar 2017 11:51:08 GMT

No need to show off 😉

Re: Current Year vs Previous Year Sort Order

Frank_Hartmann — Wed, 15 Mar 2017 12:01:02 GMT

what about this sortorder:

=Mod(Only({1}MonthName)-Min(TOTAL MonthName+1),12)+1

Re: Current Year vs Previous Year Sort Order

sunny_talwar — Wed, 15 Mar 2017 12:09:27 GMT

adamdavi3s‌ - Look who is showing off now