Aggregating Avg on a non-chart Dimension

[Anonymous]

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 😕

[sunny_talwar]

May be this

sum(Discount)/Sum(SaleValue)

-

avg(TOTAL <DayOfWeek>

  aggr(

    sum(Discount) / sum(SaleValue),

    DayOfWeek, Store

    )

  )

[Anonymous · Accepted solution]

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!