Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

12 Month Avg in combo Chart

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.

 

Tags (1)
2 Solutions

Accepted Solutions
Highlighted

Re: 12 Month Avg in combo Chart

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)

View solution in original post

Highlighted

Re: 12 Month Avg in combo Chart

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)

View solution in original post

4 Replies
Highlighted

Re: 12 Month Avg in combo Chart

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)

View solution in original post

Highlighted
Contributor II
Contributor II

Re: 12 Month Avg in combo Chart

It works!! Thanks!!

Highlighted
Contributor II
Contributor II

Re: 12 Month Avg in combo Chart

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. 

Highlighted

Re: 12 Month Avg in combo Chart

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)

View solution in original post