Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
Expecting your reply.
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
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...!
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
where should i give this FLAG option
Hi,
You have to use
Qunatity is -> =sum(PQTY-MQTY)
Value is -> IF( sum(PQTY-MQTY) = 0, 0 , sum(PSTOCKVALUE-MSTOCKVALUE) )
Rgds
Anand
still same answer is coming
sum of rows are not equal to total value
Hi,
See the attached sample file
Let me know about this.
Rgds
Anand
hi
in straight table it is showing correct. please try in Pivot chart.
thanks
kumar
Hi,
see the attached file ,it is working fine.
HI,
See the attached it is working for both
Rgds
Anand