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:
I've attached a screenshot to try and show the issue.
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.