16 Replies Latest reply: Oct 21, 2011 4:42 AM by kum.raams

# Pivot chart sub total conditions

Hi

In Pivot chart, I have dimension (location,item) , expressions(quantity,value). Here, I have value but quantity is zero for some items. I need to cut this value when quantity is zero. after that sub total(show partial columns - by checked) is having the total value(including value of zero quantity). So please give me suggestions.

my expression for Qunatity is == sum(PQTY-MQTY)

my expression for Value is == sum(PSTOCKVALUE-MSTOCKVALUE)

Location         Item                 Qty           Value

Stores            Apple              5                   10

orange             12                  15

banana           0                    3

grapes            0                     2

pineapple          2                   6

total                19                  36

i need

total                19                  31

Thanks

Kumar

• ###### Pivot chart sub total conditions

Hi,

If you need to show as below:

Location         Item                 Qty           Value

Stores            Apple              5                   10

orange             12                  15

pineapple          2                   6

total                19                  31

Use these Expressions:

Qty:sum(if(PQTY-MQTY>0,PQTY-MQTY))

Value:sum(if(PQTY-MQTY>0,PSTOCKVALUE-MSTOCKVALUE))

hope this helps you...!

• ###### Pivot chart sub total conditions

hi

try this

in Script:

if(Qty=0 0r isnull(Qty='-1'),'0','1') as Flag

in expression:

sum(Qty*Flag)

Sum(Value*Flag)

may it hepls you

thanks

rohit

• ###### Pivot chart sub total conditions

where should i give this FLAG option

• ###### Pivot chart sub total conditions

Hi,

You have to use

Qunatity is ->  =sum(PQTY-MQTY)

Value is -> IF( sum(PQTY-MQTY) = 0, 0 , sum(PSTOCKVALUE-MSTOCKVALUE) )

Rgds

Anand

• ###### Pivot chart sub total conditions

sum of rows are not equal to total value

• ###### Re: Pivot chart sub total conditions

Hi,

See the attached sample file

Rgds

Anand

• ###### Pivot chart sub total conditions

hi

in straight table it is showing correct. please try in Pivot chart.

thanks

kumar

• ###### Re: Pivot chart sub total conditions

Hi,

see the attached file ,it is working fine.

• ###### Pivot chart sub total conditions

this is correct. but for my case, i need to put sum() inside IF cndtion that "PSTOCKVALUE-MSTOCKVALUE".

so tat inside values will be correct.

Kumar

• ###### Re: Pivot chart sub total conditions

HI,

See the attached it is working for both

Rgds

Anand

• ###### Re: Pivot chart sub total conditions

pls find attachmnt

• ###### Re: Pivot chart sub total conditions

Hi,

It is correct in chart Stock 3 it is correct.

Rgds

Anand

• ###### Re: Pivot chart sub total conditions

but dispayed records total is not equal to sub total.

pls chk.

Kumar

• ###### Re: Pivot chart sub total conditions

hi

use flag in your edit script

thanks

rohit

• ###### Re: Pivot chart sub total conditions

while reloading it is showing error. i checked in expression it is showing error for isnull().

• ###### Re: Pivot chart sub total conditions

pls give me sugesstions

kumar