# 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

## 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)
Labels (3)

• ### QLIKVIEW chart expressions

2 Solutions

Accepted Solutions
Highlighted
MVP

## 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)``
Highlighted
MVP

## 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)``
4 Replies
Highlighted
MVP

## 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)``
Highlighted
Contributor II

## Re: 12 Month Avg in combo Chart

It works!! Thanks!!

Highlighted
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
MVP

## 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)``