topic Re: Aggr / Total / No Distinct in QlikView

Aggr / Total / No Distinct

gavin_kite — Wed, 22 Nov 2017 16:38:49 GMT

I'm trying to calculate the percentage of a product Sales by Month by Store (See % of Sales column) so need to group by Store and Month but not the product

Store	Product	Month	Gross Value	% of Sales
A	1	2017-01	£100	22% (calc is 100/450)
A	2	2017-01	£150	33% (calc is 150/450)
A	3	2017-01	£200	44% (calc is 200/450)
A	1	2017-02	£300	38% (calc is 300/520)
A	2	2017-02	£120	23% (calc is 120/520)
A	3	2017-02	£100	19% (calc is 100/520)
B	1	2017-01	£40	33% (calc is 40/120)
B	2	2017-01	£50	42% (calc is 50/120)
B	3	2017-01	£30	25% (calc is 30/120)
B	1	2017-02	£60	28% (calc is 60/210)
B	2	2017-02	£80	38% (calc is 80/210)
B	3	2017-02	£70	33% (calc is 70/210)

I've tried

Sum(Gross Value)

Aggr (Sum( Gross Value),Store,Month)

And have also made various attempts using "Total" and "NoDistinct"

Aggr(Total Sum(Gross Value),Store,Month)

Aggr(NoDistinct Sum(Gross Value),Store,Month)

Does anyone have any suggestions or see what I'm missing?

Thanks

Re: Aggr / Total / No Distinct

sunny_talwar — Wed, 22 Nov 2017 16:46:17 GMT

Try this

Sum([Gross Value])/Sum(TOTAL <Store, Month> [Gross Value])

Re: Aggr / Total / No Distinct

YoussefBelloum — Wed, 22 Nov 2017 16:46:26 GMT

Hi,

maybe the £ sign create a problem here..

if so:

=sum(PurgeChar([Gross Value],'£'))/ sum(TOTAL <Store,Month> PurgeChar([Gross Value],'£'))

if no:

=sum([Gross Value])/ sum(TOTAL <Store,Month> [Gross Value])

Re: Aggr / Total / No Distinct

gavin_kite — Wed, 22 Nov 2017 17:48:46 GMT

Thanks for your reply

So if I'd also got Set Analysis in the Expression

Would it look this this?

Sum({<ProdCat={01}>}[Gross Value])/Sum({<ProdCat={01}>}TOTAL <Store, Month> [Gross Value])

Re: Aggr / Total / No Distinct

sunny_talwar — Wed, 22 Nov 2017 17:50:31 GMT

Yup, looks good to me

Re: Aggr / Total / No Distinct

gavin_kite — Thu, 23 Nov 2017 10:07:06 GMT

Boom! Worked a dream. Thank you

Re: Aggr / Total / No Distinct

vikasmahajan — Wed, 28 Feb 2018 11:47:50 GMT

Thanks Sunny this help me !!

Vikas