Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

gavin_kite
New Contributor II

Aggr / Total / No Distinct

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

StoreProductMonthGross Value% of Sales
A12017-01£10022% (calc is 100/450)
A22017-01£15033% (calc is 150/450)
A32017-01£20044% (calc is 200/450)
A12017-02£30038% (calc is 300/520)
A22017-02£12023% (calc is 120/520)
A32017-02£10019% (calc is 100/520)
B12017-01£4033% (calc is 40/120)
B22017-01£5042% (calc is 50/120)
B32017-01£3025% (calc is 30/120)
B12017-02£6028% (calc is 60/210)
B22017-02£8038% (calc is 80/210)
B32017-02£7033% (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"

Eg

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

1 Solution

Accepted Solutions

Re: Aggr / Total / No Distinct

Try this

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

6 Replies

Re: Aggr / Total / No Distinct

Try this

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

YoussefBelloum
Esteemed Contributor

Re: Aggr / Total / No Distinct

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])

gavin_kite
New Contributor II

Re: Aggr / Total / No Distinct

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

Yup, looks good to me

gavin_kite
New Contributor II

Re: Aggr / Total / No Distinct

Boom! Worked a dream. Thank you

vikasmahajan
Honored Contributor III

Re: Aggr / Total / No Distinct

Thanks Sunny this help me !!

Vikas

Community Browser