Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I'm looking for some help on Aggr. I thought i had a pretty good knowledge of Aggr but this has stumped me!
I have total sales by MM - YY and I have discounts, as you can see from the table below, apart from the date, the 2 numbers aren't related in the data model
Current Position
Month Year | Discount Type | Discount Amount | Total Sales | Discount % |
---|---|---|---|---|
Jan 2017 | - | - | 10,000 | - |
Jan 2017 | Coupon | 500 | - | - |
Jan 2017 | Staff | 1000 | - | - |
Jan 2017 | Till Discount | 200 | - | - |
Feb 2017 | - | - | 20000 | - |
Feb 2017 | Coupon | 300 | - | |
Feb 2017 | Staff | 600 | - | |
Feb 2017 | Till Discount | 500 | - | - |
What I'd like to do is to work out at discount type level, what percentage of discount has been given as a percentage of total sales. Feels like this should be achievable with Aggr but I'm not having much luck
Required Solution
Month Year | Discount Type | Discount Amount | Total Sales | Discount % |
---|---|---|---|---|
Jan 2017 | - | - | 10,000 | - |
Jan 2017 | Coupon | 500 | 10,000 | 5% |
Jan 2017 | Staff | 1000 | 10,000 | 10% |
Jan 2017 | Till Discount | 200 | 10,000 | 2% |
Feb 2017 | - | - | 20,000 | - |
Feb 2017 | Coupon | 300 | 20,000 | 2% |
Feb 2017 | Staff | 600 | 20,000 | 3% |
Feb 2017 | Till Discount | 500 | 20,000 | 3% |
My attempt so far has been
Exp 1
Sum ([Discount Amount])
/
Sum(Aggr(Sum([Total Sales]),[Month Year])
Exp 2
Sum ([Discount Amount])
/
Sum(Total Aggr(Sum([Total Sales]),[Month Year])
Can anyone help me out?
Thanks
use aggr(nodistinct ...)
use aggr(nodistinct ...)
Thanks for the reply
This did the trick
Sum ([Discount Amount])
/
Aggr(NoDistinct Sum([Total Sales]),[Month Year])