Complex Aggregation Help

[#1]

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

[abeyphilip]

Hi Kaushik,

Please see is the attached qvw helps

Regarrds,

Abey

[#3]

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.

[abeyphilip]

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

[#5]

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 ?

[#6]

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

Abey Chandy