topic Re: Wrong total value in pivot table . in QlikView

Wrong total value in pivot table .

Wed, 14 Oct 2015 12:51:16 GMT

Dear Experts ,

I am working on finance oriented report , every things work fine when i select parameter . if my parameter (term_code) selection is null , then its showing wrong total value .

Please check my qvw , if i select term code as cash or credit , not due , due amount and over due showing correct total ,

if my selection is null on term code parameter , then not due column total showing value as zero and over due total is wrong .

My record value is correct , total alone showing wrong . please suggest .

Re: Wrong total value in pivot table .

hic — Wed, 14 Oct 2015 13:01:15 GMT

Your expressions contain a number of naked field references and this will cause problems. See Use Aggregation Functions!

For instance, in the following expression

if ( (TERM_CODE = 'CREDIT' or GetSelectedCount(TERM_CODE) = 0) and TERM_CODE = 'CREDIT' ,
sum(if(EFFECTIVE_DATE<= LastDay1 and LastDay1-DOCUMENT_DATE<if(len(PUBLISHED_CREDIT_DAY)=0,0,PUBLISHED_CREDIT_DAY+1) , $(vOSAmt))),0)

you have the following problems:

TERM_CODE is a naked field reference and could potentially be evaluated to NULL when it shouldn't
In the Sum() function, you have fields from several tables. This may lead to record duplication. You should use Set Analysis instead.

HIC

Re: Wrong total value in pivot table .

raajaswin — Wed, 14 Oct 2015 13:01:23 GMT

Hi Sriram,

I think now it is ok

Re: Wrong total value in pivot table .

Qrishna — Wed, 14 Oct 2015 13:03:21 GMT

Not Due:

if ( (TERM_CODE = 'CREDIT' or GetSelectedCount(TERM_CODE) = 0) and TERM_CODE = 'CREDIT' ,

sum(if(EFFECTIVE_DATE<= LastDay1 and LastDay1-DOCUMENT_DATE<if(len(PUBLISHED_CREDIT_DAY)=0,0,PUBLISHED_CREDIT_DAY+1) , $(vOSAmt))),0)

This expression tells Qlikview only to calculate when a user selects TERM_CODE,else give total as '0'

check the expression again once.

Re: Wrong total value in pivot table .

jonas_rezende — Wed, 14 Oct 2015 19:30:47 GMT

Hi, sriram santharaman.

Please, see if file attached helps.

Read the comments in each expression.

Hope this helps!

Re: Wrong total value in pivot table .

Thu, 15 Oct 2015 09:21:52 GMT

Dear Krishna ,

Your Correct , Not Due and Due Amount should want to calculate when term code is Credit , else i want to display zero.

Record wise its working fine .

How to display total when i didn't select term code parameter , i want to show the total for credit customers alone in not due and due amount .

Pls suggest .

Re: Wrong total value in pivot table .

Thu, 15 Oct 2015 09:26:53 GMT

Dear Jonas ,

thanks for your reply , its working fine for over due amount . But in not due and due amount column , i want to show the total for credit customers alone .

From your example, not due total will be 23000 but it showing 26053 , also record wise also i want to display amount for credit customers alone in not due and due amount column . so for the customer Maison de joell , not due and amount due will be zero .

Please suggest .

Re: Wrong total value in pivot table .

Thu, 15 Oct 2015 15:45:10 GMT

Hi Sriram,

try this attached file.

I couln´t check "Due Amount" column beacuse its values are 0, but i think that the expression is also correct.

Regards.