Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

oseimuduame
Contributor

Sub Totals not Correct

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.

Tags (1)
9 Replies

Re: Sub Totals not Correct

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.


talk is cheap, supply exceeds demand
Not applicable

Re: Sub Totals not Correct

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

oseimuduame
Contributor

Re: Sub Totals not Correct

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

Not applicable

Re: Sub Totals not Correct

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

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


talk is cheap, supply exceeds demand
oseimuduame
Contributor

Re: Sub Totals not Correct

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

oseimuduame
Contributor

Re: Sub Totals not Correct

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

Not applicable

Re: Sub Totals not Correct

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

Not applicable

Re: Sub Totals not Correct

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

Community Browser