topic Re: Problem with "total" in pivot table when collapsed in QlikView

Problem with "total" in pivot table when collapsed

Tue, 03 Jul 2012 10:54:25 GMT

Hello,

I have a problem with a formula in a pivot table.

I want to display the market share of the following data:

LOAD * INLINE [

Market, Product, Country, Sales

Drinks, Coca Cola, USA, 90

Drinks, Coca Cola, Canada, 10

Drinks, Water, USA, 40

Drinks, Water, Canada, 10

Food, Burger, USA, 180

Food, Burger, Canada, 20

Food, Fries, USA, 40

Food, Fries, Canada, 10

];

I use this expression to know the values of the whole market:

sum(total <Market, Country> Sales)

If all my column are expanded, I have the right values:

But if I expand only 1 value of column, my values are wrong:

See, It says that the market is "10" and "90" whereas it should be "20" and "130".

I don't understand why my values are not the same when I expand / collapse 1 or more values in the columns...

I've found a workaround by using the "aggr" function:

if(Dimensionality() = 3,
          aggr(nodistinct sum(total <Market, Country> Sales), Market, Country),
          if(Dimensionality() = 2,
                    aggr(nodistinct sum(total <Market, Country> Sales), Market),
                    sum(total <Market> Sales)
          )
)

but it's very hard to use / update when I have more than 5 columns.

Is there a better way?

Is there a way to do the same without having the "Market" in dimension and without make agregation in load?

Thank you.

Re: Problem with "total" in pivot table when collapsed

Anonymous — Wed, 04 Jul 2012 03:09:05 GMT

Hi Nicolas,

Try this expression:

sum(aggr(sum(total <Market,Country> Sales), Market, Country, Product))

Not sure if you're getting the numbers that you expect but to me it looks like it's matching up. Think it should be ok even if you take the Market dimension out of the chart.

Re: Problem with "total" in pivot table when collapsed

Wed, 04 Jul 2012 08:10:10 GMT

This gives the correct value only for the lowest level but not on the totals.

Only 1 line expanded:

All line expanded:

Re: Problem with "total" in pivot table when collapsed

newuser — Fri, 12 Apr 2019 01:31:25 GMT

I'm having a similar issue (attached is the QVF with dummy data). Has anyone figured out a way to address it? When the lowest-level dimension is totally expanded, the totals by region change vs. when the lowest-level dimension is collapsed.