Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am building the table shown below.
Column C = B/30 - this is the average over the last 30 weeks
I am only interested in showing those rows where C is greater than the value selected in E (this is stored in a variable call vThreshold)
I have added in the expression for column 😧
= if([Shipments Last 30 weeks (Average)] >= vThreshold,
(([Shipments Last Week (Actual)] - [Shipments Last 30 weeks (Average)]) / [Shipments Last 30 weeks (Average)])
)
This has had the effect of not calculating column D for those under the threshold but not hiding the row which was my intent - any suggestions would be welcome.
Regards
Alexis
Hello Alexis,
You can't use a column as condition for showing or not your value if this column is already showing or not a value based on a condition.
The workaround is to write the full expression.
Let's say your column A is
sum(Shipments)
your column B is
sum(Last30)
and your column C is
B/30
Then, you modify your expressions:
In the column C (it's the key expression), you don't use references to other columns, but full expressions:
if(sum(Last30) / 30 > vThreshold, sum(Last30) / 30)
your column A will be
if(C, sum(Shipments))
your column B will be
if(C, sum(Last30))
your column D will be
if(C, (A - C) / C)
(for A, B and D, you can use column name instead of full expressions)
Best regards,
Nicolas
Hi Alexis,
vThreshold should be $(vThreshold)
Andy
Hi Andrew,
Thanks for the reply - changed it to $(vThreshold) and it still shows the rows where C is less than threshold.
regards
Alexis
Ah, your calc only restricts what appears to column d.
Andy
Are you trying to make the whole row disappears when the value in column c is less than the v threshold value?
YES - that is certainly what I want ...
The issue here is that I am deciding whether to include or exclude a row based on an expression that I calculate on the fly (the average shipments in this case) - however, I am sure that this is a common requirement but have spent hours on this already with no luck!
Hello Alexis,
You can't use a column as condition for showing or not your value if this column is already showing or not a value based on a condition.
The workaround is to write the full expression.
Let's say your column A is
sum(Shipments)
your column B is
sum(Last30)
and your column C is
B/30
Then, you modify your expressions:
In the column C (it's the key expression), you don't use references to other columns, but full expressions:
if(sum(Last30) / 30 > vThreshold, sum(Last30) / 30)
your column A will be
if(C, sum(Shipments))
your column B will be
if(C, sum(Last30))
your column D will be
if(C, (A - C) / C)
(for A, B and D, you can use column name instead of full expressions)
Best regards,
Nicolas
Perfect answer Nicolas - works perfectly - THANK YOU!