topic Re: Subtotals for multiple expressions in pivot table? in QlikView

Subtotals for multiple expressions in pivot table?

Thu, 27 Feb 2014 15:54:03 GMT

Hi,

I have a pivot table for annual sales forecasts, showing the 12 months of the year as the horizontal dimension, and the expression "sum(ForecastAmt)". I've been asked to add a second expression, which is "Sum(ForecastAmt)*ForecastProbability".

My pivot table shows correct sums for "Annual Total" at the far right, and the Monthly Totals for the first expression are correct. However, the monthly totals for the new expression don't show up, nor do the annual totals add up for the second expression to a grand total. Any thoughts?

Re: Subtotals for multiple expressions in pivot table?

Thu, 27 Feb 2014 16:26:54 GMT

Can you try sum(ForecastAmt*ForecastProbability).

The whole expression needs to be aggregated. In this case its sum().

Thanks

Re: Subtotals for multiple expressions in pivot table?

Thu, 27 Feb 2014 16:28:53 GMT

Thanks, AJ! Worked perfectly.

Re: Subtotals for multiple expressions in pivot table?

Anonymous — Thu, 27 Feb 2014 16:33:27 GMT

you might have to use agggregate function for the dimension where you want the total . For example if you want the total to be at month. then your expression would be sum(aggr(sum(ForecastAmt*ForecastProbability),Month)). If this does solve pleae post the QVW doc with some sample data.

Re: Subtotals for multiple expressions in pivot table?

veidlburkhard — Thu, 27 Feb 2014 16:46:27 GMT

Hi Kevin: try this expression for your 'ProbAmt';

Sum(Aggr(Sum(Amount) * Prob, [Opp Name])).

Look at the simple example below:

Hope this helps,

Regards

Burkhard