topic Re: How do I force a calculation at a level not displayed in QlikView

How do I force a calculation at a level not displayed

Wed, 17 Oct 2012 10:20:45 GMT

I have a complex data structure, as most people do - I have Deals that have multiple Items, the Deals also have multiple Categories (I call them details in the attached qvw). I need to create a factor using the weights in the Categories them mutiply it by the Item Amounts.

When all the dimension data is displayed, I'm able to get the correct total.

When I drop some of the dimensions, the math changes - How do I create an expresion to force the factor to ALWAYS take the sum of the weights selected for a deal and divide by the total weight of the deal?

I'm new to QV and have tried several options but have not come close! Any ideas or help would be appreciated.

Thanks

Re: How do I force a calculation at a level not displayed

swuehl — Wed, 17 Oct 2012 10:32:25 GMT

You should look into advanced aggregation, i.e. the aggr() function which allows to calculate an expression in different context than the chart dimension. In your case, try an expression like

=aggr(Sum(Weight)/Sum( Total <DealID>Weight),ItemID,DealID,Item,State)

for Factor in your second chart.

Hope this helps,

Stefan

Re: How do I force a calculation at a level not displayed

Wed, 17 Oct 2012 12:18:40 GMT

The aggr function works for the middle chart when deal is not displayed, what about when only the state or item are displayed?

Does the order of the items listed in the aggr function matter?

Thanks for the quick reply.