topic 12 Month Avg in combo Chart in QlikView

12 Month Avg in combo Chart

Krish — Sat, 16 Nov 2024 00:53:21 GMT

I am creating a combo chart which will have revenue across 12 months as bar graph and total rev/12 months as Line graph.

the Revenue is rolling 12 month number.

rev = sum({< MonthYear ={ "$(='>=' & Date(AddMonths(Max(MonthYear), -11), 'MMM-YY') & '<=' & Date(Max(MonthYear), 'MMM-YY'))"},Year,Month>}Revenue)

The Line graph needs to be a single straight line (for eg: if rev = 12000, and yearmonth = dec 2019, then line graph sould be a straight line of 1000 for all months ). but i am not able to achieve this. how can this be achieved.

Re: 12 Month Avg in combo Chart

sunny_talwar — Tue, 07 Apr 2020 11:09:51 GMT

You are having trouble with the line expression? May be this

Sum(TOTAL {< MonthYear ={ "$(='>=' & Date(AddMonths(Max(MonthYear), -11), 'MMM-YY') & '<=' & Date(Max(MonthYear), 'MMM-YY'))"}, Year, Month>} Revenue)/Count(DISTINCT Month)

Re: 12 Month Avg in combo Chart

Krish — Tue, 07 Apr 2020 11:24:05 GMT

It works!! Thanks!!

Re: 12 Month Avg in combo Chart

Krish — Tue, 07 Apr 2020 11:34:17 GMT

This works fine when only year is selected, but not when Month is selected.

If 2019 is selected, this works. But when Oct is selected, the line graph should find the avg. pf oct 2019 till nov 2018.

Re: 12 Month Avg in combo Chart

sunny_talwar — Tue, 07 Apr 2020 11:49:22 GMT

Try this

Sum(TOTAL {<MonthYear ={ "$(='>=' & Date(AddMonths(Max(MonthYear), -11), 'MMM-YY') & '<=' & Date(Max(MonthYear), 'MMM-YY'))"}, Year, Month>} Revenue)/Count(DISTINCT {<MonthYear ={ "$(='>=' & Date(AddMonths(Max(MonthYear), -11), 'MMM-YY') & '<=' & Date(Max(MonthYear), 'MMM-YY'))"}, Year, Month>} Month)