Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rolling YearMonth does not work in Chart




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:


             ((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]))


                                      ((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]))



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(
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)
                 ((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]))



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?



2 Replies

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.



Not applicable


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.