Aggr Advice Required

gavin_kite — Mon, 03 Jul 2017 15:33:24 GMT

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

Re: Aggr Advice Required

Anonymous — Mon, 03 Jul 2017 15:41:54 GMT

use aggr(nodistinct ...)

Re: Aggr Advice Required

gavin_kite — Mon, 03 Jul 2017 15:53:35 GMT

Thanks for the reply

This did the trick

Sum ([Discount Amount])

Aggr(NoDistinct Sum([Total Sales]),[Month Year])

topic Re: Aggr Advice Required in QlikView

Aggr Advice Required

Re: Aggr Advice Required

Re: Aggr Advice Required