Hi
For those of you who don’t know about rolling month plz read this guide instead: https://community.qlik.com/docs/DOC-4252#start=50
This rolling expression works in a textbox for the current month, but not in a chart, because it cannot split it out on each dimension/month:
//Calculation1
((Count({$<CalendarSource={'2'},YearMonth,Year,Month,Quarter,MonthSerial={"<=$(=Max(MonthSerial)-0)>=$(=Max(MonthSerial)-5)"}, Field1={‘test’}, Field2= {'F2A'}>} total DISTINCT [Count Field])
/
Count({$<CalendarSource={'2'},YearMonth,Year,Month,Quarter,MonthSerial={"<=$(=Max(MonthSerial)-0)>=$(=Max(MonthSerial)-5"}, Field1 ={‘test’} ,Field2= {'F2A','F2B','F2C'}>} total DISTINCT [Count Field]))
*100)
-
//Calculation2
((Count({$<CalendarSource={'2'},YearMonth,Year,Month,Quarter,MonthSerial={"<=$(=Max(MonthSerial)-0)>=$(=Max(MonthSerial)-5)"},Field1={‘test’}, CustomerCategory = {'F2C'}>} total DISTINCT [Count Field])
/
Count({$<CalendarSource={‘2’,YearMonth,Year,Month,Quarter,MonthSerial={"<=$(=Max(MonthSerial)-0)>=$(=Max(MonthSerial)-5)"},Type={‘test’}, CustomerCategory = {'F2A','F2B','F2C'}>} total DISTINCT [Count Field]))
*100)
For the textbox I don’t even need the total, but I need it for the line chart (actually a combo). With nothing selected I get a straight line for all dimensions, but the number is only the same correct number for the current month as in the texbox. If I select YearMonth I only get this current selected, but with other expressions in the chart I geta full straight line and then only at the last selected/max month it rises to the correct number:
It should of cause change for each month with the selected as max/end month.
So I tried this Expression instead:
(Edit: I solved it, of course I need to use the full set all the places).
Round(Sum({<CalendarSource={'2'},YearMonth,Year,Month,Quarter,MonthSerial={"<=$(=Max(MonthSerial)-0)>=$(=Max(MonthSerial)-5)"}>} Aggr(
RangeAvg(
Above( //Calculation1
((Count({$<CalendarSource={'2'},YearMonth,Year,Month,Quarter,MonthSerial={"<=$(=Max(MonthSerial)-0)>=$(=Max(MonthSerial)-5)"}, Field1={‘test’}, Field2= {'F2A'}>} DISTINCT [Count Field])
/
Count({$<CalendarSource={'2'},YearMonth,Year,Month,Quarter,MonthSerial={"<=$(=Max(MonthSerial)-0)>=$(=Max(MonthSerial)-5)"}, Field1 ={‘test’} ,Field2= {'F2A','F2B','F2C'}>} DISTINCT [Count Field])) *100)
-
//Calculation2
((Count({$<CalendarSource={'2'},YearMonth,Year,Month,Quarter,MonthSerial={"<=$(=Max(MonthSerial)-0)>=$(=Max(MonthSerial)-5)"}, Field1={‘test’}, CustomerCategory = {'F2C'}>} DISTINCT [Count Field]) /
Count({$<CalendarSource={'2'},YearMonth,Year,Month,Quarter,MonthSerial={"<=$(=Max(MonthSerial)-0)>=$(=Max(MonthSerial)-5)"}, Type={‘test’}, CustomerCategory = {'F2A','F2B','F2C'}>} DISTINCT [Count Field]))
*100)
,0,6))
,YearMonth)))
Like this I get different results for each month, but not the correct ones, though for the selcted/current/max month I get approximately the same, but not good enough.
If I add the TOTAL before DISTINCT, again I get the correct number for the selected/max month, but with the exact same problem as with the first expression, so it did not get me further.
Any ideas plz?
