topic Show/Hide with If Condition in QlikView

Show/Hide with If Condition

rkpatelqlikview — Fri, 15 Dec 2017 16:13:06 GMT

Hi,

Hope you are doing great..

How will get symbols based on this weekly analysis when clicking on th "Rank" OR "CA" OR "TX" etc..

I used 'If' condition for comparing current week and previous week but when the previous week not there its taking the value 0 then result is showing wrong symbols.

I wrote below condition in symbols layout.

This is for GreenThumbsUP.

=if(vscore=0,1,0)

=if(ceil( sum({$<WEEK={$(=Max(WEEK))}>} $(sumkm))/sum({$<WEEK={$(=Max(WEEK))}>}[Kms Travelled])*1000) & '%'

ceil( sum({$<WEEK={$(=Max(WEEK)-1)}>}$(sumkm))/sum({$<WEEK={$(=Max(WEEK)-1)}>}[Kms Travelled])*1000) & '%', 1,0)

IF analysis Constance.->Yellow exclamation mark ->Put the above same expression in layout and changed '=' and If Increased -> RedThumbs down like below.

Please help on this expression.

Expression is not working when the previous week absence. Thanks in Advance.

Re: Show/Hide with If Condition

YoussefBelloum — Fri, 15 Dec 2017 16:25:02 GMT

Extract of portion of your data and attach it if it is possible. or the app

Re: Show/Hide with If Condition

Peter_Cammaert — Fri, 15 Dec 2017 16:27:48 GMT

But what do you want to happen when there is no data for the previous week? If you want all symbols to disappear, then add a preliminary check to your IF() for a zero condition, in which case your return 0.

Also, drop the & '%' and *1000 parts from the comparison expressions. Comparing 100% to 80% is the same as comparing the underlying ratios 1.0 to 0.8

Re: Show/Hide with If Condition

Peter_Cammaert — Fri, 15 Dec 2017 16:33:21 GMT

For example, this expression should work for the GreenThumbsUP symbol:

=IF (Ceil(Sum({$<WEEK={$(=Max(WEEK)-1)}>}$(sumkm))/Sum({$<WEEK={$(=Max(WEEK)-1)}>}[Kms Travelled])) <> 0,

IF (Ceil(Sum({$<WEEK={$(=Max(WEEK))}>} $(sumkm))/Sum({$<WEEK={$(=Max(WEEK))}>}[Kms Travelled])) <

Ceil(Sum({$<WEEK={$(=Max(WEEK)-1)}>}$(sumkm))/Sum({$<WEEK={$(=Max(WEEK)-1)}>}[Kms Travelled])),

1, 0),

Re: Show/Hide with If Condition

rkpatelqlikview — Sat, 16 Dec 2017 03:33:09 GMT

Thanks Peter for your quick response.

for above IF condition is applicable when the last week is not equals to 0. if it's 0 it should compare with =Max(week)-2 (if this one also <> 0) if this =Max(week)-2 is equals 0 then it should compare with =Max(week)-3 like that.HOw to write for these cases?

Thanks in advance.

Re: Show/Hide with If Condition

rkpatelqlikview — Tue, 19 Dec 2017 02:46:43 GMT

Hi Peter,

Thanks for your reply. I wrote this one in object layout. Please let me know this if condition is correct or not.

=IF(vscore=0,1,0)

=IF(ceil( sum({$<WEEK={$(=Max(WEEK)-1)}>}$(sumkm))/sum({$<WEEK={$(=Max(WEEK)-1)}>}[Kms Travelled])*1000)<>0,

if(ceil( sum({$<WEEK={$(=Max(WEEK))}>} $(sumkm))/sum({$<WEEK={$(=Max(WEEK))}>}[Kms Travelled])*1000)

ceil( sum({$<WEEK={$(=Max(WEEK)-1)}>}$(sumkm))/sum({$<WEEK={$(=Max(WEEK)-1)}>}[Kms Travelled])*1000), 1,

IF(ceil( sum({$<WEEK={$(=Max(WEEK)-1)}>}$(sumkm))/sum({$<WEEK={$(=Max(WEEK)-1)}>}[Kms Travelled])*1000)=0 and

ceil( sum({$<WEEK={$(=Max(WEEK)-2)}>}$(sumkm))/sum({$<WEEK={$(=Max(WEEK)-2)}>}[Kms Travelled])*1000)<>0,

IF(ceil( sum({$<WEEK={$(=Max(WEEK))}>} $(sumkm))/sum({$<WEEK={$(=Max(WEEK))}>}[Kms Travelled])*1000)

< ceil( sum({$<WEEK={$(=Max(WEEK)-2)}>}$(sumkm))/sum({$<WEEK={$(=Max(WEEK)-2)}>}[Kms Travelled])*1000),1,

IF(ceil( sum({$<WEEK={$(=Max(WEEK)-2)}>}$(sumkm))/sum({$<WEEK={$(=Max(WEEK)-2)}>}[Kms Travelled])*1000)=0 and

ceil( sum({$<WEEK={$(=Max(WEEK)-3)}>}$(sumkm))/sum({$<WEEK={$(=Max(WEEK)-3)}>}[Kms Travelled])*1000)<>0,

IF(ceil( sum({$<WEEK={$(=Max(WEEK))}>} $(sumkm))/sum({$<WEEK={$(=Max(WEEK))}>}[Kms Travelled])*1000)

< ceil( sum({$<WEEK={$(=Max(WEEK)-3)}>}$(sumkm))/sum({$<WEEK={$(=Max(WEEK)-3)}>}[Kms Travelled])*1000),1,0))))))

Is this expression correct?

Re: Show/Hide with If Condition

Peter_Cammaert — Tue, 19 Dec 2017 08:34:56 GMT

This may work, and the easiest way to test it would be to display the week data in numerical format, select different Week Numbers and see what happens with the symbol that carries this expression.

There may also be an easier method (using the Above() function) but in order to create an expression like that, it would be nice if you could upload an example document with some test data.