help with expressions

[surajap123]

Hi Experts,

I have created a chart with necessary expressions. Please help me with the expressions of below req-

1. Expression showing average of the 3 expression, as a 4th column
2. Expression showing Increase or decrease Arrow, compare to previous month.

Thanks!

[sunny_talwar]

Can you share the exact expression you tried?

[surajap123]

if(RangeAvg({<MonthNum = {"$(=Max(MonthNum))"}>}Column(1), Column(2), Column(3)) >=
RangeAvg({<MonthNum = {"$(=Max(MonthNum)-1)"}Column(1), Column(2), Column(3)),'qmem://<bundled>/BuiltIn/arrow_n_g.png', 'qmem://<bundled>/BuiltIn/arrow_s_r.png')

[sunny_talwar]

You cannot use set analysis in RangeAvg() function... you will have to use the complete expressions if you don't want to update your individual expressions to add the set analysis in them

=If(RangeAvg(

Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store1_S)/Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store1_E)*100,

Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store2_S)/Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store2_E)*100,

Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store3_S)/Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store3_E)*100) >=

RangeAvg(

Sum({<MonthNum = {"$(=Max(MonthNum)-1)"}>}Store1_S)/Sum({<MonthNum = {"$(=Max(MonthNum)-1)"}>}Store1_E)*100,

Sum({<MonthNum = {"$(=Max(MonthNum)-1)"}>}Store2_S)/Sum({<MonthNum = {"$(=Max(MonthNum)-1)"}>}Store2_E)*100,

Sum({<MonthNum = {"$(=Max(MonthNum)-1)"}>}Store3_S)/Sum({<MonthNum = {"$(=Max(MonthNum)-1)"}>}Store3_E)*100),

'qmem://<bundled>/BuiltIn/arrow_n_g.png',

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

[surajap123]

Thanks Sunny. The expression is working.  But the only concern is, if maxMonth >=PreviousMonth  returns 0 or null, then I don't want to show any arrow, instead I want to show the string 'n/a' in that cell.

Thanks!

[sunny_talwar]

Suraj -

Have you tried making the changes on your end? I have no problem making changes, but how are you going to learn anything if I will give you each and every single thing. Do you want to give it a shot and if it doesn't work, I am here to help. I would suggest you to share whatever you have tried and then we can look at it together.

Best,

Sunny

[surajap123]

Thanks for your suggestion Sunny. I totally agree. !

I tried it, please find my expression in the attached app.

[sunny_talwar]

Check the attached

[surajap123]

Hi Sunny,

Sorry for not being clear.

I want n/a only for Rubber as you can see the average of all the 3 main expressions gives null.

The pen and Pencil should give either up or down arrow  as its main expression has values so average of those will return value, so the up or down arrow should be based on maxMonth >= PreviousMonth condition.

eg-This expression gives null for Rubber, so our 4th expression should return n/a in this case.

RangeAvg(
Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store1_S)/Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store1_E)*100,
Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store2_S)/Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store2_E)*100,
Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store3_S)/Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store3_E)*100
)

Thanks!

[sunny_talwar · Accepted solution]

Do you need all the three to be present for the arrows to show or one of the three?? If it is the later, you can try this

=If(Sum(Store1_S)/Sum(Store1_E)*100 >= 0 and Sum(Store2_S)/Sum(Store2_E)*100 >= 0 and Sum(Store3_S)/Sum(Store3_E)*100 >= 0,

If(RangeAvg(

Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store1_S)/Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store1_E)*100,

Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store2_S)/Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store2_E)*100,

Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store3_S)/Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store3_E)*100) >=

RangeAvg(

Sum({<MonthNum = {"$(=Max(MonthNum)-1)"}>}Store1_S)/Sum({<MonthNum = {"$(=Max(MonthNum)-1)"}>}Store1_E)*100,

Sum({<MonthNum = {"$(=Max(MonthNum)-1)"}>}Store2_S)/Sum({<MonthNum = {"$(=Max(MonthNum)-1)"}>}Store2_E)*100,

Sum({<MonthNum = {"$(=Max(MonthNum)-1)"}>}Store3_S)/Sum({<MonthNum = {"$(=Max(MonthNum)-1)"}>}Store3_E)*100

),'qmem://<bundled>/BuiltIn/arrow_n_g.png',

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

Otherwise, use this

=If(Sum(Store1_S)/Sum(Store1_E)*100 >= 0 or Sum(Store2_S)/Sum(Store2_E)*100 >= 0 or Sum(Store3_S)/Sum(Store3_E)*100 >= 0,

If(RangeAvg(

Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store1_S)/Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store1_E)*100,

Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store2_S)/Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store2_E)*100,

Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store3_S)/Sum({<MonthNum = {"$(=Max(MonthNum))"}>}Store3_E)*100) >=

RangeAvg(

Sum({<MonthNum = {"$(=Max(MonthNum)-1)"}>}Store1_S)/Sum({<MonthNum = {"$(=Max(MonthNum)-1)"}>}Store1_E)*100,

Sum({<MonthNum = {"$(=Max(MonthNum)-1)"}>}Store2_S)/Sum({<MonthNum = {"$(=Max(MonthNum)-1)"}>}Store2_E)*100,

Sum({<MonthNum = {"$(=Max(MonthNum)-1)"}>}Store3_S)/Sum({<MonthNum = {"$(=Max(MonthNum)-1)"}>}Store3_E)*100

),'qmem://<bundled>/BuiltIn/arrow_n_g.png',

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

[surajap123]

Thanks you so much for all the help Sunny. I really appreciate it.