Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Partial Sums in Pivot Tables

Hi,

I have the following pivot table with 2 dimensions one of which is created using ValueList() function as given below.

=ValueList('Capping Volume', 'Top Retail Deals', 'Top Retail Deals Ordered', 'Top Retail Deals vs. CAP volume')

And my expression is as below

=pick(

   match(ValueList('Capping Volume',

   'Top Retail Deals',

   'Top Retail Deals Ordered',

   'Top Retail Deals vs. CAP volume'),

  'Capping Volume',

  'Top Retail Deals',

  'Top Retail Deals Ordered',

  'Top Retail Deals vs. CAP volume'),

   SUM(Capping),

   SUM({<Top_Retail = {'Y'}>} quantity),

   SUM({<Top_Retail = {'Y'}>} IF(NOT IsNull(Ordered), quantity)),

      SUM({<Top_Retail = {'Y'}>} quantity)/SUM(Capping)

     )

I want to see the Partial Sums (EMEA) for Region which I have done from presentation tab of pivot table. And the partial sum is working for first three rows. But in the 4th row, It does not sum up the values of the row instead it is dividing the value of partial sum of first two rows.

So my question is, how do I get the summation of the row values in the last row?

Thanks for the help.

Best Regards,

Andrew Hudson.

3 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Partial Sums in Pivot Tables

Try aggregating that last expression over the Region dimension:

sum(aggr(SUM({<Top_Retail = {'Y'}>} quantity)/SUM(Capping),Region))


talk is cheap, supply exceeds demand
Highlighted
Creator II
Creator II

Re: Partial Sums in Pivot Tables

I tried as per your suggestion and changed the expression as below

=pick(

   match(ValueList('Capping Volume',

   'Top Retail Deals',

   'Top Retail Deals Ordered',

   'Top Retail Deals vs. CAP volume'),

  'Capping Volume',

  'Top Retail Deals',

  'Top Retail Deals Ordered',

  'Top Retail Deals vs. CAP volume'),

   SUM(Capping),

   SUM({<Top_Retail = {'Y'}>} quantity),

   SUM({<Top_Retail = {'Y'}>} IF(NOT IsNull(Ordered), quantity)),

      SUM(Aggr(SUM({<Top_Retail = {'Y'}>} quantity)/SUM(Capping), Subs))

     )

But after that, the last row disappears.

Note: Subs is actual dimension name and I renamed it in the dimension tab.

Is there any other way to achieve it?

Highlighted
MVP & Luminary
MVP & Luminary

Re: Partial Sums in Pivot Tables

Please post a document that demonstrates the problem.


talk is cheap, supply exceeds demand