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.

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

Rolling YearMonth does not work in Chart

 

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?

 

 

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.

Best,

Sunny

Not applicable

Hi

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.