Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Complex Aggregation Help

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?

TerritoryProductMarketProduct SalesMarket Sales
AProduct1Primary Market10100
AProduct1Secondary Market1020
AProduct1Secondary Market2030
5 Replies
abeyphilip
Creator II
Creator II

Hi Kaushik,

Please see is the attached qvw helps

Regarrds,

Abey

Not applicable
Author

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 2Secondary Market1Primary MarketMarket Volume
Territory 115001016003110
Territory 2500550105

District=(Territory1,Terrritory2)

Required(On Rollup)

3215
Obtained20001516503315

I have tried using the formula as indicated in the QVW that you attached.

abeyphilip
Creator II
Creator II

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

Not applicable
Author

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 ?

Not applicable
Author

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