Skip to main content
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
Showing results for 
Search instead for 
Did you mean: 
Partner - Contributor III
Partner - Contributor III

Average Over Time by the Department of the selected Dimensions


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

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]))