Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
Need your help with respect to this.
At a most granular level I have product and market with their product sales and market sales . I want the Market Volume to be calculated for the product based on a formula like
Primary Market+Secondary Market1+min(Secondary Market2,Primary Market)
For example:
For territory A and Product1
Market Volume=100+20+min(30,100)=150
This calculation has to be done for each territory,product combination and the roll up on district or region should also work .
Can we do this in Qlikview?
Territory | Product | Market | Product Sales | Market Sales |
---|---|---|---|---|
A | Product1 | Primary Market | 10 | 100 |
A | Product1 | Secondary Market1 | 0 | 20 |
A | Product1 | Secondary Market2 | 0 | 30 |
Hi Kaushik,
Please see is the attached qvw helps
Regarrds,
Abey
Hi Abey,
Thanks for your speedy reply .
I have a doubt here . If we use any other dimension for the chart .Lets say District instead of territory .
So Now we want the market volume of each District.
The market volume should be calculated on the districts and then the roll ups should happen .
Is that possible.
The way it works right now is that it rolls up the different market (Primary,secondary 1 and secondary 2) first and then uses the formula .
I want just the roll ups on the district .Please look a the below example
Secondary Market 2 | Secondary Market1 | Primary Market | Market Volume | |
Territory 1 | 1500 | 10 | 1600 | 3110 |
Territory 2 | 500 | 5 | 50 | 105 |
District=(Territory1,Terrritory2) Required(On Rollup) | 3215 | |||
Obtained | 2000 | 15 | 1650 | 3315 |
I have tried using the formula as indicated in the QVW that you attached.
Kaushik,
I am not sure if you are referring to the partial sum option. For that you will have to make the chart as pivot and then in presentation tab you will get the option for enabling the partial sum for the required dimension (in this case District).
If that is not the issue, please post a sample qvw.
Regards,
Abey
Hi Abey .
I dont think partial sum will help .Please see the attached QVW
The market volume should be 3215 instead of 3315. Please also see my previous comment which I have updated.
I am not a pro in Qlikview but does the answer lie in using aggr function ?
HI Abey,
Nailed it .Thanks for your help .
The aggr function based on territory first creates a virtual table where we can aggregate based on our formula.
Then we can do the rollup on district using Sum().
Please look at the attached QVW