Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
hope one of you can help me with this
I have a pivot table with
articlecode current stock sales/month
1341012S `18 43
28
41
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
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
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
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
Thanks Daniele,
that was what I was looking for
The dynamical formula works perfect!!
Thank you all for swift replies!!
Hello Anita,
I'm glad you have found useful the solution!
Regards,
Daniele
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??
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
no, this doesn't work
do I use it in the script or in the table (tried both, but maybe not corect...?)
Did you used this in backround expression?
yes, but did not work