Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

gavin_kite
New Contributor II

Aggr Advice Required

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 YearDiscount TypeDiscount AmountTotal SalesDiscount %
Jan 2017--10,000-
Jan 2017Coupon500--
Jan 2017Staff1000--
Jan 2017Till Discount200--
Feb 2017--20000-
Feb 2017Coupon300-
Feb 2017Staff600-
Feb 2017Till Discount500--

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 YearDiscount TypeDiscount AmountTotal SalesDiscount %
Jan 2017--10,000-
Jan 2017Coupon50010,0005%
Jan 2017Staff100010,00010%
Jan 2017Till Discount20010,0002%
Feb 2017--20,000-
Feb 2017Coupon30020,0002%
Feb 2017Staff60020,0003%
Feb 2017Till Discount50020,0003%

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

1 Solution

Accepted Solutions
roharoha
Valued Contributor III

Re: Aggr Advice Required

use aggr(nodistinct ...)

2 Replies
roharoha
Valued Contributor III

Re: Aggr Advice Required

use aggr(nodistinct ...)

gavin_kite
New Contributor II

Re: Aggr Advice Required

Thanks for the reply

This did the trick

Sum ([Discount Amount])

/

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

Community Browser