topic Re: Sub Totals not Correct in QlikView

Sub Totals not Correct

oseimuduame — Mon, 01 Aug 2016 15:53:15 GMT

Good Evening guys,

i just noticed that my PIVOT table Sum is Not correct... if i sum what i have in my table its not correlating with the SUM i am having.

please any help..

Below is my Projects

Thank You.

Re: Sub Totals not Correct

Gysbert_Wassenaar — Mon, 01 Aug 2016 16:23:51 GMT

I'm not downloading 46MB just to have a look. So I'm going to guess that your expression isn't a simply sum and therefore doesn't give the same result at the total level as the sum of the rows. If you can use a straight table instead of a pivot table you can set the Total Mode to Sum of rows. The pivot table doesn't have that option. So you need something like sum(aggr( ...your_expression... , Dim1, Dim2, ...., DimN)) where Dim1, Dim2, ...., DimN are the dimensions of your pivot table. And if that doesn't work then your expression is referencing other chart expressions by their label names. In which case you'll need to replace them in your incorrectly-summing-expression with the actual expressions instead of the expression label names.

Re: Sub Totals not Correct

Mon, 01 Aug 2016 16:25:53 GMT

The Total make the same calculo of you expression...you can chage the expression of total with if(dimensionality()=0,total,expression)

Re: Sub Totals not Correct

oseimuduame — Mon, 01 Aug 2016 17:08:35 GMT

This is actually what i need. Thank you

its working very perfectly now.....

One More thing, i am trying to use Equal to but it seems not to work here is my expression:

if ((CUST_CREATED_DATE <> CUST_LAST_SALES_DATE), Count (CUST_ID))

i have a dashboard with Shopping Customers also i have Created CUstomers, i want to have a dashboard that will show me the number of old customers that actually came shopping, not the new customer that came shopping (more or less like a RETURNEE DASHBOARD.

Any help Please

Re: Sub Totals not Correct

Mon, 01 Aug 2016 17:27:34 GMT

Count (if ((CUST_CREATED_DATE <> CUST_LAST_SALES_DATE), CUST_ID))

but this is not performatic. i sugest creat a new fiel on script and use a set analisys on expression

load if (CUST_CREATED_DATE <> CUST_LAST_SALES_DATE,1) as Flag,

...

from source

expression:

count({<Flag={1}>} CUST_ID)

Re: Sub Totals not Correct

Gysbert_Wassenaar — Mon, 01 Aug 2016 17:29:05 GMT

<> means not equal. = means equal. You're using not equal instead of equal.

Re: Sub Totals not Correct

oseimuduame — Mon, 01 Aug 2016 18:19:54 GMT

i Used the Flag but my expression is underlined in Red though it gave me Result.... But my Total Under the PIVOT is still not correct i tried using the Sum(aggr(count({<Flag={1}>} CUST_ID))) but not working.

Any ideas

Re: Sub Totals not Correct

oseimuduame — Mon, 01 Aug 2016 18:20:22 GMT

Yes... i want to get Not Equal to Not the Equal to.

Re: Sub Totals not Correct

Mon, 01 Aug 2016 18:53:48 GMT

you need put your dimensions on aggr

Sum(aggr(count({<Flag={1}>} CUST_ID),Dim1,Dim2,Dim3))

You need put the same dimensions of your put in your pivot table

Re: Sub Totals not Correct

Tue, 02 Aug 2016 12:24:17 GMT

if you got a correct answer select one to keep organized community