topic Re: formula in pivottable based on subtotal in QlikView

formula in pivottable based on subtotal

anitamanders — Fri, 26 Oct 2012 09:36:40 GMT

Hello,

hope one of you can help me with this

I have a pivot table with

articlecode current stock sales/month

1341012S `18 43

subtotal 112

What i want is that current stock turns red if the average sales for 3 months (when you select 3 months) is more than current stock

so: sum(sales)/3 > stock than RED

I have enclosed my document

Example code 1341012S

Current stock =18

avv. for month july/aug/sept = 37,3333 (=112/3)

stock should turn red, but it isn't

when you only select month june (where sales is >18) than it is working, so it seems my formula only looks at the sales and not at subtotal sales

Can someone help me??

Thanks

Re: formula in pivottable based on subtotal

atafsson — Fri, 26 Oct 2012 11:42:30 GMT

Hi,

I dont really understand what the fields in your example stands for, but look at the tab 'Visual Cues' in the properties for the table-object.

You can put an expression to compare two different columns against each other.

Hope it helps!

/Axel

Re: formula in pivottable based on subtotal

Fri, 26 Oct 2012 14:47:31 GMT

Hello,

please find attached the modified version of your document. I have added a table and solved your problem.

I have changed this "=if (sum(aantgelev)/3>(voorraad),red(200),green(200))" with "=if (sum(Total aantgelev)/3>(voorraad),red(200),green(200))". In your example you need the keyword Total to consider all the Sales.

I have also added a new table with a dynamical formula so you can select 1 or n months and you will always have the correct average of Sales.

Hope it's useful.

Regards,

Daniele

Re: formula in pivottable based on subtotal

anitamanders — Mon, 29 Oct 2012 08:34:26 GMT

Thanks Daniele,

that was what I was looking for

The dynamical formula works perfect!!

Thank you all for swift replies!!

Re: formula in pivottable based on subtotal

Mon, 29 Oct 2012 10:47:16 GMT

Hello Anita,

I'm glad you have found useful the solution!

Regards,

Daniele

Re: formula in pivottable based on subtotal

anitamanders — Mon, 29 Oct 2012 13:08:42 GMT

Hi Daniele,

one more question maybe you can help me again...

articles with stock = 0 do not turn red, why is that???

eg. code 7194000

As if QV reads 0 not as a number?

I have tried num#(voorraad), but this does not work

Can you help our??

Re: formula in pivottable based on subtotal

CELAMBARASAN — Mon, 29 Oct 2012 13:34:52 GMT

Hi,

For that code(7194000) stock is null.

The value(0,000) shown to you is Null symbol you have mentioned in the Presentation tab.

So you have to use Alt function for this situation

use

Alt(voorrad,0) instead of voorraad or Num#(voorraad)

Hope it helps

Re: formula in pivottable based on subtotal

anitamanders — Mon, 29 Oct 2012 13:52:33 GMT

no, this doesn't work

do I use it in the script or in the table (tried both, but maybe not corect...?)

Re: formula in pivottable based on subtotal

CELAMBARASAN — Mon, 29 Oct 2012 14:51:16 GMT

Did you used this in backround expression?

Re: formula in pivottable based on subtotal

anitamanders — Mon, 29 Oct 2012 15:04:05 GMT

yes, but did not work

Re: formula in pivottable based on subtotal

CELAMBARASAN — Mon, 29 Oct 2012 15:38:55 GMT

Can you upload ur file?

Re: formula in pivottable based on subtotal

anitamanders — Mon, 29 Oct 2012 15:50:23 GMT

I have added a file in my original post

Daniele added this with my first question

When you select code 7194000 you can see stock = null

and box should turn red, but stays green

Re: formula in pivottable based on subtotal

MayilVahanan — Mon, 29 Oct 2012 16:01:20 GMT

Please check the attached file.

Re: formula in pivottable based on subtotal

anitamanders — Tue, 30 Oct 2012 08:30:33 GMT

super! thanks

I added the Alt in the wrong place, but this works!

thanks