Pivot Table - Total problem

[#1]

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.

[Miguel_Angel_Baeyens]

Hi,

Try using instead:

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

Hope that helps.

Miguel

[#3]

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.

[Miguel_Angel_Baeyens]

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

[#5]

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