Show/Hide with If Condition

[rkpatelqlikview]

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.

[YoussefBelloum]

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

[Peter_Cammaert]

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

[Peter_Cammaert]

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),

0)

[rkpatelqlikview]

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.

[rkpatelqlikview]

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?

[Peter_Cammaert]

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.