topic Re: Aggregating Avg on a non-chart Dimension in App Development

Aggregating Avg on a non-chart Dimension

Anonymous — Fri, 25 Aug 2017 09:37:17 GMT

That's a bit of a mouthful for a title, my apologies I couldn't figure out how to word it more simply.

I'm using Qlik Sense, and have a sample app (attached) with data for Store, Day of Week, Discount and Sale Value. In a bar chart, my dimension is a concatenation of the Store and DayOfWeek. Each bar needs to show the percentage Discount vs Sale Value ... sum(Discount)/Sum(SaleValue) ... minus the average percentage Discount/Sale Value for all stores for that day. My expression at the moment looks like this:-

sum(Discount)/Sum(SaleValue) - avg(aggr(sum(Discount) / sum(SaleValue), DayOfWeek))

And it appears to be working, sometimes. My sample data has 2 stores, but the calculated value only appears on the (red) bars for Euston Rd, there's nothing for Park Lane (pic below tells the story).

Any assistance would be greatly appreciated, this has been bending my brain for hours 😕

Re: Aggregating Avg on a non-chart Dimension

sunny_talwar — Fri, 25 Aug 2017 13:38:32 GMT

May be this

sum(Discount)/Sum(SaleValue)

avg(TOTAL <DayOfWeek>

aggr(

sum(Discount) / sum(SaleValue),

DayOfWeek, Store

)

Re: Aggregating Avg on a non-chart Dimension

Anonymous — Fri, 25 Aug 2017 14:08:00 GMT

Thanks, but that's not quite it. If I remove the subtraction, so the equation on the red bars is:-

avg(TOTAL <DayOfWeek>

aggr(

sum(Discount) / sum(SaleValue),

DayOfWeek, Store

)

I should see the same values for each day as listed in the table to the right. Instead, they're all 19.1% - its calculating the total average, not the average for each day.

HOWEVER, your response has gotten me past the mental wall that prevented me seeing the solution. The correct answer is:

sum(Discount)/Sum(SaleValue)

avg(

aggr(

sum(TOTAL <DayOfWeek> Discount) / sum(TOTAL <DayOfWeek> SaleValue),

DayOfWeek, Store

)

Thanks for your help Sunny!