Rolling YearMonth does not work in Chart

Mon, 27 Jul 2015 01:49:13 GMT

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?

Re: Rolling YearMonth does not work in Chart

sunny_talwar — Mon, 27 Jul 2015 01:54:03 GMT

It would be more helpful to see the application where it is getting used instead of just looking at the expressions. Would it be possible for you to share a sample with expected output? I understand creating a sample can be a hassle at time, but I think it would be easier and quicker to get a solution that ways.

Best,

Sunny

Re: Rolling YearMonth does not work in Chart

Mon, 27 Jul 2015 02:15:49 GMT

Sorry, but the application is confidential and kind of complicated, so it will take some time to make a dummy application, but if it is the only way, it might come in some days, but I would hope for an answer before that.

topic Re: Rolling YearMonth does not work in Chart in QlikView

Rolling YearMonth does not work in Chart

Re: Rolling YearMonth does not work in Chart

Re: Rolling YearMonth does not work in Chart