Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Partner
Partner

Average Over Time by the Department of the selected Dimensions

HI,

I would like to create a line chart which has

Year-Month as dimension and

measures are Profit Ratio (of Current Selection) and

Average Profit Ratio for the Department of the selected Product at that point in time (for each of the Month).

When a User selects a Product/Employee the chart should display the Profit Ratio over time along with the average Profit Ratio of the Department over-time so it will be easy to compare data in the same chart.

Profit Ratio = Sum(Income) / Sum(Sales)

The Department average shows the Overall as a single dot instead of showing a line chart Over Time.

Average Profit Ratio for the Department Formula:


Sum(Aggr(Sum({$<[$(=Concat({1<$Field-={'Date','Year','Year Month','Year Quarter'}>}distinct $Field,']=,[')&']=')>} [Income]),[Department]))

/

Sum(Aggr(Sum({$<[$(=Concat({1<$Field-={'Dat','Year','Year Month','Year Quarter'}>}distinct $Field,']=,[')&']=')>} [Sales]),[Department]))

Can you please help me to get the industry average over time as separate line..

1 Reply

Re: Average Over Time by the Department of the selected Dimensions

Try this may be and see if this gives you what you want

Sum(Aggr(Sum(TOTAL <[Year-Month]> {$<[$(=Concat({1<$Field-={'Date','Year','Year Month','Year Quarter'}>}distinct $Field,']=,[')&']=')>} [Income])/Sum(TOTAL <[Year-Month]> {$<[$(=Concat({1<$Field-={'Dat','Year','Year Month','Year Quarter'}>}distinct $Field,']=,[')&']=')>} [Sales]), [Department], [Year-Month]))