Wrong total value in pivot table .

[#1]

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 .

[hic]

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

[raajaswin]

Hi Sriram,

  I think now it is ok

[Qrishna]

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.

[jonas_rezende]

Hi, sriram santharaman.

Please, see if file attached helps.

Read the comments in each expression.

Hope this helps!

[#6]

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 .

[#7]

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 . 

[#8 · Accepted solution]

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.