topic Re: Totals not correct in QlikView

Totals not correct

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have used pivot table but the totals are not matching.Below is the expression.

Expression:

if(sum(total_dec_app) > sum(aggr(sum(customer_id_count),store)),sum(total_dec_app) - sum(aggr(sum(customer_id_count),store)),0)

Below is the image for reference.

Thanks..

Re: Totals not correct

Anil_Babu_Samineni — Mon, 02 Jan 2017 04:58:31 GMT

When you say Total is not matched, You may describe more. And then, I would recommend to share sample data and expected output which demonstrates the issue

Expression seems okay to me

Re: Totals not correct

nareshthavidishetty — Mon, 02 Jan 2017 05:13:22 GMT

Yes the total is not matching..

Re: Totals not correct

PrashantSangle — Mon, 02 Jan 2017 05:52:51 GMT

Hi,

try below.

1: sum(aggr(sum(if(total_dec_app > customer_id_count,total_dec_app - customer_id_count,0),store))

or
2: if(sum(total_dec_app) > sum(aggr(sum(customer_id_count),store)),sum(aggr(sum(total_dec_app),store)) - sum(aggr(sum(customer_id_count),store)),0)

Regards

Re: Totals not correct

HirisH_V7 — Mon, 02 Jan 2017 06:19:37 GMT

Hi,

Can you post your sample data and elaborate your requirement.

I suggest to use dimensionality() for the totals fields. It will be helpful for grouping and using aggr efficiently in pivot.

-Hirish

Re: Totals not correct

satheshreddy — Mon, 02 Jan 2017 12:47:04 GMT

Hi Naresh,

can you check the below EXP.

if(sum(total_dec_app) > aggr(sum(customer_id_count),store),sum(total_dec_app) - aggr(sum(customer_id_count),store),0)

Regards

Sathish

Re: Totals not correct

Mon, 02 Jan 2017 18:24:26 GMT

Hi,

It is a bad approach to use if statements in your expressions as it is killing the app performance. Looking at the expression it might work as per design.

Please create a copy of your table and remove the store dimension. Then you will likely see the subtotals as you currently see.

Also you can add two expressions to see the behavior of the complete formula.statement. Expression 1 only contains: sum(total_dec_app). expression 2 contains sum(aggr(sum(customer_id_count),store)).

Kind regards,

Matthijs

Re: Totals not correct

sunny_talwar — Mon, 02 Jan 2017 22:39:39 GMT

How many dimensions do you have in your pivot table? Just 1 or are there more?