topic Re: Conditional display of rows in table in QlikView

Conditional display of rows in table

alexis — Tue, 28 Mar 2017 13:02:25 GMT

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

Re: Conditional display of rows in table

awhitfield — Tue, 28 Mar 2017 13:08:29 GMT

Hi Alexis,

vThreshold should be $(vThreshold)

Andy

Re: Conditional display of rows in table

alexis — Tue, 28 Mar 2017 13:14:36 GMT

Hi Andrew,

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

regards

Alexis

Re: Conditional display of rows in table

awhitfield — Tue, 28 Mar 2017 13:23:25 GMT

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

Andy

Re: Conditional display of rows in table

awhitfield — Tue, 28 Mar 2017 13:31:36 GMT

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

Re: Conditional display of rows in table

alexis — Tue, 28 Mar 2017 13:38:21 GMT

YES - that is certainly what I want ...

Re: Conditional display of rows in table

alexis — Tue, 28 Mar 2017 14:59:09 GMT

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!

Re: Conditional display of rows in table

nicolas_martin — Wed, 29 Mar 2017 10:31:05 GMT

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

Re: Conditional display of rows in table

alexis — Wed, 29 Mar 2017 22:28:43 GMT

Perfect answer Nicolas - works perfectly - THANK YOU!