Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Current Year vs Previous Year Sort Order

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-MonthCYPY
2015-Mar0210
2015-Apr0171
2015-May0189
2015-Jun0244
2015-Jul0228
2015-Aug0169
2015-Sep0224
2015-Oct0224
2015-Nov0222
2015-Dec0183
2016-Jan0210
2016-Feb0243
2016-Mar2130
2016-Apr2040
2016-May2020
2016-Jun2120
2016-Jul2180
2016-Aug1960
2016-Sep2230
2016-Oct1970
2016-Nov2260
2016-Dec1740
2017-Jan2160
2017-Feb207

0

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

Can anyone point me in the right direction?

Many thanks,

Phil

1 Solution

Accepted Solutions
Frank_Hartmann
Master II
Master II

what about this sortorder:

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

View solution in original post

23 Replies
adamdavi3s
Master
Master

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!

Not applicable
Author

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.

adamdavi3s
Master
Master

Hi Phil,

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

Not applicable
Author

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.

2017-03-14_12h01_50.png

Many thanks,

Phil

adamdavi3s
Master
Master

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

Frank_Hartmann
Master II
Master II

can u prepare a small example qvw to play around.

i guess this way you will get a solution much faster

adamdavi3s
Master
Master

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

adamdavi3s
Master
Master

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

)

adamdavi3s
Master
Master

stalwar1‌ save me from myself.....