topic Re: Rolling 24 months static in QlikView

Rolling 24 months static

vardhancse — Tue, 15 Dec 2015 14:02:13 GMT

Hi,

Have one requirement is that, need to create one trend

it should be statis 24 months based on the selected year-23 months

given expression as below:

Sum

Sum({<FiscalYear={$(=Max(FiscalYear)),$(=Max(FiscalYear)-1)},MonthName=,Metric={'Enablement Cost (A$m)'}>}Sales)

now i am getting static 24 months trend based on the selected year.

but have one issue is that, the trend was showing only for the data available.

Requirement is that, data available or not the months should display.

can please help me out.

Re: Rolling 24 months static

JonnyPoole — Tue, 15 Dec 2015 14:15:50 GMT

Do you have any other date related fields in your application that may be narrowing the time span ? For example below, you may need to cancel out other selections that are date related.

Sum({<FiscalYear={$(=Max(FiscalYear)),$(=Max(FiscalYear)-1)},MonthName=,Date=,Quarter=,Metric={'Enablement Cost (A$m)'}>}Sales)

If you clear selections does it work as you want ?

Re: Rolling 24 months static

vardhancse — Tue, 15 Dec 2015 14:29:06 GMT

Hi,

Thanks for your reply.

in the line chart, given dim was fymonth(jan-13,feb-13.....)

and in the expression :

Sum({<FiscalYear={$(=Max(FiscalYear)),$(=Max(FiscalYear)-1)},MonthName=,Metric={'Enablement Cost (A$m)'}>}Sales)

tried adding the other date fields, but till which month data was there, the trend was showing till there only.

Re: Rolling 24 months static

JonnyPoole — Tue, 15 Dec 2015 14:45:52 GMT

Ok. i don't see any change to your expression so i'm not sure what you tried ?

Also you mentioned the dimension is 'fymonth'. If that is the field name then that is an example of a field that should be canceled out in the SET ANALYSIS like below:

Sum({<FiscalYear={$(=Max(FiscalYear)),$(=Max(FiscalYear)-1)},fymonth=,MonthName=,Metric={'Enablement Cost (A$m)'}>}Sales)

I also didn't get a response on whether this works fine if you clear the selections. Can you share that ?

Re: Rolling 24 months static

vardhancse — Tue, 15 Dec 2015 16:17:35 GMT

hi,

Let me try once and will get back to you.

Thank you