Announcements
cancel
Showing results for
Did you mean:
Partner - Specialist

## Conditional display of rows in table

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

1 Solution

Accepted Solutions
Partner - Creator II

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)

 sum(Last30)

 B/30

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)

 if(C, sum(Shipments))

 if(C, sum(Last30))

 if(C, (A - C) / C)

(for A, B and D, you can use column name instead of full expressions)

Best regards,

Nicolas

8 Replies
Partner - Champion

Hi Alexis,

vThreshold should be \$(vThreshold)

Andy

Partner - Specialist
Author

Hi Andrew,

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

regards

Alexis

Partner - Champion

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

Andy

Partner - Champion

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

Partner - Specialist
Author

YES - that is certainly what I want ...

Partner - Specialist
Author

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!

Partner - Creator II

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)

 sum(Last30)

 B/30

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)

 if(C, sum(Shipments))

 if(C, sum(Last30))

 if(C, (A - C) / C)

(for A, B and D, you can use column name instead of full expressions)

Best regards,

Nicolas

Partner - Specialist
Author

Perfect answer Nicolas - works perfectly - THANK YOU!

Community Browser