
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
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"
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
- Tags:
- qlikview_scripting
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this
Sum([Gross Value])/Sum(TOTAL <Store, Month> [Gross Value])

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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])

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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])

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yup, looks good to me

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Boom! Worked a dream. Thank you

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Sunny this help me !!
Vikas
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
