Trend based on nested if

[surajdhall]

Hi All,

I am having trouble building a expression.

I have a pivot table with month as horizontal dimension and the expression is sum(Margin)/sum(Sales). I want to show up/down/Horizontal arrow in second expression, based on below conditions-

1)If Feb is > Jan then show UP Arrow.

2)If Feb is Equal to Jan then show UP Arrow when both are 100%. If both are Equal but less than 100% then show horizontal arrow.

Eg- Both Feb is 38% and Jan is 38% then show horizontal arrow.

Also, i want to apply rounding of 0.005 on Feb. ie. if Feb is within threshold of 0.005 then it will be considered equal to Jan so show horizontal arrow or UP arrow if both are 100% after rounding on Feb.

3)If Feb is < than jan show Down Arrow.

4)If latest month ie.Feb is null, then Down arrow

5)If Previous month ie. Jan is null and Feb has value then show UP arrow

6) If both Jan and Feb is null then show blank/null

I have build the below expression, but it is not working.

=if(After(sum(Margin)/sum(Sales)) > (sum(Margin)/sum(Sales)),'qmem://<bundled>/BuiltIn/arrow_n_g.png',

if((sum(Margin)/sum(Sales)) = Round(After(sum(Margin)/sum(Sales)),'0.005'),'qmem://<bundled>/BuiltIn/arrow_e.png',

'qmem://<bundled>/BuiltIn/arrow_s_r.png'))

I really appreciate if anyone could help me.

Thanks!

[sunny_talwar]

Which of arrows not working? Can you point out?

[dinuwanbr]

Hi Suraj,

According ur conditions given; arrows are working fine to me.

Rgds,

Tharindu

[surajdhall]

Hi All, !

-2nd row is going UP. I expect it to go Horizontal.

-8th row(Product H), i expect to go UP, as Jan has no data and Feb has Data

-9th row(Product I), i expect to show blank, as both jan and feb data is not present.

-Also all arrows in Feb is going DOWN, as there is no march data, the column show show blank.

Also when I add below 2 rows to inline load, I see they give 100% in both jan and feb but shows DOWN arrow, instead of horizontal arrow.

H, 01/01/2017, 11983605, 11983879
H, 01/02/2017, 11590671, 11590945
]

Regarding the round(), I am not sure if the round() I did on the new month is correct. Do i have to do Round() on both New and Previous month like below. My requirement is to bring Feb near to Jan to see if both match, if they match show Horzontal arrow.

Round(sum(Margin)/sum(Sales)),'0.005') = Round(After(sum(Margin)/sum(Sales)),'0.005')

Thanks!

[sunny_talwar · Accepted solution]

Check this

If(Len(Trim(After(Sum(Margin)/Sum(Sales)))) > 0 or Len(Trim(Sum(Margin)/Sum(Sales))) > 0,

If(Round(Alt(After(Sum(Margin)/Sum(Sales)), 0), 0.0001) > Round(Alt(Sum(Margin)/Sum(Sales), 0), 0.0001), 'qmem://<bundled>/BuiltIn/arrow_n_g.png',

If(Round(Alt(Sum(Margin)/Sum(Sales), 0), 0.0001) = Round(Alt(After(Sum(Margin)/Sum(Sales)), 0), 0.0001), 'qmem://<bundled>/BuiltIn/arrow_e.png', 'qmem://<bundled>/BuiltIn/arrow_s_r.png')))

[surajdhall]

Hi Sunny,

Thank you so much.

The expression seems working, I will try this in the mail application and get back in few minutes

But I am just wondering why did you round with 0.0001?. My requirement is to round using 0.005. Is it ok to use 0.0001?

[sunny_talwar]

Rounding to 0.005 was not working for what you wanted your arrows to do.... besides 0.0001 is rounding the percentage to two decimals... meaning

10.344523 % = 0.10344523... do you want this to be 10.34% or 10.5%? because 0.005 will round the number to 10.5% making it more forgiving in matching the values... is that what you want?

[surajdhall]

Thanks for you suggestion.

I want 2 decimals in percentage 10.34%. I will go with 0.0001 as you suggested.

One final help.  I want to show 2 overall trend columns for High & Low( as per below conditions) beside the Product dimension, like below image. As it is pivot table, i cannot able to create as expression, so i hope it would be calculated dimension using Aggr(), but not sure what conditions to put, as there columns are only for display purpose.

The conditions are-

If no Down arrows for the Product and Status, then show 'Good trend'

If Down arrows are less then 2, show 'positive trend'

If Down arrows are greater than 1, show 'Average Trend'

I added the status column in the app.

Please suggest.

[sunny_talwar]

Like this?

[surajdhall]

Thank you so much for the help