Conditional display of rows in table

[alexis]

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

[awhitfield]

Hi Alexis,

vThreshold should be $(vThreshold)

Andy

[alexis]

Hi Andrew,

Thanks for the reply - changed it to $(vThreshold) and it still shows the rows where C is less than threshold.

regards

Alexis

[awhitfield]

Ah, your calc only restricts what appears to column d.

Andy

[awhitfield]

Are you trying to make the whole row disappears when the value in column c is less than the v threshold value?

[alexis]

YES - that is certainly what I want ...

[alexis]

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!

[nicolas_martin · Accepted solution]

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

[alexis]

Perfect answer Nicolas - works perfectly - THANK YOU!