topic Re: Pivot Table - Total problem in QlikView

Pivot Table - Total problem

Mon, 17 Dec 2012 15:53:13 GMT

Hi guys:

I have a problem, which i thought will be a small issue but it is driving me crazy.

Please help.

I am using a pivot table.

I have 3 dimensions say Dim1 , Dim2 and Status .I have an expression Sum(m1)

the totals in this case come correct.

Dim1 Dim2 Status Sum(m1)

A AA T1 10

B BB T2 20

C CC T3 30

Total 60

but my requirement is that when status = 'T2' then Sum(m1) should display 0 and should not be added to the total.

but the strange thing is when i write my expression-

If(Status = 'T2',0,Sum(m1))

My 2nd row(Dim1=B,Dim2 = BB,Status = T2) is displaying 0 but total is still coming as 60. I would have expected it to be 40.

did you guys face this problem.

Please suggest what i am missing here.

Re: Pivot Table - Total problem

Miguel_Angel_Baeyens — Mon, 17 Dec 2012 16:07:57 GMT

Hi,

Try using instead:

Sum(If(Status = 'T2', 0, m1))

Hope that helps.

Miguel

Re: Pivot Table - Total problem

Mon, 17 Dec 2012 16:53:09 GMT

Miquel:

Thanks for the quick response.

In my question earlier, i had written simple if for the sake of example.

My actual Expression has set modifiers also, so i have something like

if(Status_SOURCE = 2,0,Sum({<ADGT_TYPE = {'Budget'},AMT_ID = {'Month'}>}AMOUNT))

so now if i apply your suggestion,how the expression should look

Sum(if(Status_SOURCE = 2,0,Sum({<ADGT_TYPE = {'Budget'},AMT_ID = {'Month'}>}AMOUNT)))

or Sum(if(Status_SOURCE = 2,0,{<ADGT_TYPE = {'Budget'},AMT_ID = {'Month'}>}AMOUNT)))

both of these give me error.

Re: Pivot Table - Total problem

Miguel_Angel_Baeyens — Mon, 17 Dec 2012 17:06:24 GMT

Yup,

Syntax with set modifiers and a conditional is a bit different:

Sum({<ADGT_TYPE = {'Budget'}, AMT_ID = {'Month'} >} If(Status_SOURCE = 2, 0, AMOUNT))

In the future, think of writing the expression with a field, then replace the field with the If() to make things simpler.

Hope that helps.

Miguel

Re: Pivot Table - Total problem

Mon, 17 Dec 2012 18:46:13 GMT

Miquel:

if i follow the above syntax, my Amount is kind of doubling up.

I also used your suggestion of using the field i.e.doing my calculation in the script and replacing it in the if()

It is still showing huge amount.

I calculated a field with all my conditions as F1_Amount

In Pivot expression, if i use

If(Status_SOURCE = 2,0,Sum(F1_Amount)) - I dont get the desired result i.e. i see 0 but the original amount gets added up to the total.

if i use Sum(if(Status_SOURCE = 2,0,F1_Amount)) - amounts get doubled up