topic Re: Pivot chart sub total conditions in QlikView

Pivot chart sub total conditions

Fri, 21 Oct 2011 06:42:41 GMT

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

Pivot chart sub total conditions

Fri, 21 Oct 2011 06:59:19 GMT

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

rohit214 — Fri, 21 Oct 2011 07:00:53 GMT

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

Fri, 21 Oct 2011 07:05:53 GMT

where should i give this FLAG option

Pivot chart sub total conditions

its_anandrjs — Fri, 21 Oct 2011 07:06:55 GMT

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

Fri, 21 Oct 2011 07:09:57 GMT

still same answer is coming

sum of rows are not equal to total value

Re: Pivot chart sub total conditions

its_anandrjs — Fri, 21 Oct 2011 07:14:03 GMT

Hi,

See the attached sample file

Let me know about this.

Rgds

Anand

Pivot chart sub total conditions

Fri, 21 Oct 2011 07:16:45 GMT

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

thanks

kumar

Re: Pivot chart sub total conditions

Fri, 21 Oct 2011 07:24:22 GMT

Hi,

see the attached file ,it is working fine.

Re: Pivot chart sub total conditions

its_anandrjs — Fri, 21 Oct 2011 07:31:58 GMT

HI,

See the attached it is working for both

Rgds

Anand

Pivot chart sub total conditions

Fri, 21 Oct 2011 07:34:48 GMT

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

Fri, 21 Oct 2011 07:41:24 GMT

pls find attachmnt

Re: Pivot chart sub total conditions

its_anandrjs — Fri, 21 Oct 2011 07:59:54 GMT

Hi,

It is correct in chart Stock 3 it is correct.

Rgds

Anand

Re: Pivot chart sub total conditions

Fri, 21 Oct 2011 08:22:17 GMT

but dispayed records total is not equal to sub total.

pls chk.

Kumar

Re: Pivot chart sub total conditions

rohit214 — Fri, 21 Oct 2011 08:25:33 GMT

use flag in your edit script

thanks

rohit

Re: Pivot chart sub total conditions

Fri, 21 Oct 2011 08:27:21 GMT

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

Re: Pivot chart sub total conditions

Fri, 21 Oct 2011 08:42:26 GMT

pls give me sugesstions

kumar