topic Re: Using AGGR() in Line chart in QlikView

Using AGGR() in Line chart

analyticsuser — Mon, 11 Jan 2021 20:08:04 GMT

Hello,

I'm trying to display 12 months of data in a line chart by using AGGR() in set expression. however, it works well in a table but in line chart it doesn't display as needed.

For instance, my expr - avg(aggr(sum({<year={'Curr Year'}>} Sales),DeptName) gives the right value in a straight table with Month as Dimension

How can I get it to work in a line chart for all 12 months?

Re: Using AGGR() in Line chart

Anil_Babu_Samineni — Mon, 11 Jan 2021 20:12:05 GMT

Perhaps this, If you have Date Field, Get Month field and use like this

avg(aggr(sum({<Month={">$(=AddYears(Max(Month), -1))<=$(=Max(Month))"}>} Sales),DeptName))

Re: Using AGGR() in Line chart

analyticsuser — Mon, 11 Jan 2021 20:29:29 GMT

That didn't work. I want the chart to display the average of Sales for all departments from Jan-Dec with Month as Dimension. In a table, it works well but not in line chart.

Re: Using AGGR() in Line chart

Anil_Babu_Samineni — Mon, 11 Jan 2021 20:30:55 GMT

It should work for line chart as well until unless you have some other conditions enabled.

Re: Using AGGR() in Line chart

analyticsuser — Tue, 12 Jan 2021 02:23:54 GMT

I just have Month as Dimension and using the above expression to display in the graph for each month. When there's no month selection, I would like the graph to show sales by department by each month.

Re: Using AGGR() in Line chart

Anil_Babu_Samineni — Tue, 12 Jan 2021 18:25:21 GMT

If you required by department add dimension as department and then expression should be

avg(TOTAL <Month> aggr(sum({<Month={">$(=AddYears(Max(Month), -1))<=$(=Max(Month))"}>} Sales),DeptName))