Calculate the average of percentages

[Susan_DS]

Hi, all,

I need to calculate the average of percentages for a chart in Qlik Sense. My data includes dimensions State and City, and measures budget, forecast spend, delta (delta = budget minus forecast spend), and delta percent (delta percent = delta divided by budget).

This gives me data like the following:

I would like to calculate the average of the delta % column, to use as a reference line in a chart. What is the correct way to do this in Qlik Sense?

Thanks!

[Kushal_Chawda]

In line chart properties - Add-On -> Reference line->Add Reference line and put below expression in reference line expression

avg(aggr(((Budget Expression) - (Forecast Expression))/(Budget Expression), Dimension))

Where Dimension is your Line chart dimension

[Susan_DS]

Hi, Kush,

Thanks for your reply! I have used your syntax below and set up the expression as:

avg(aggr(((Budget) - (Forecast Spend))/(Budget), State, City)). The expression editor says 'OK' - but nothing shows up on the chart. I have checked the "Show" box on the Reference Lines properties box. I think my expression is missing something. Any suggestions?

For reference, I'm using Qlik Sense September 2019.

Thanks.

[Kushal_Chawda]

Please share a sample file to look at it