Solved: help with expressions - Qlik Community

surajap123 · ‎2017-03-15

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 · ‎2017-03-16

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

View solution in original post

sunny_talwar · ‎2017-03-15

For the 1st question, you can use RangeAvg()

=RangeAvg(Column(1), Column(2), Column(3))

for the 2nd part, do you need an arrow for all four expressions? or just the final expression?

surajap123 · ‎2017-03-15

Hi Sunny,

Thanks for the expressions and your response.

Please provide me average expression if i want to put in a different chart wtihout the other 3 expressions.

Yes, please provide both arrow and final expression.

sunny_talwar · ‎2017-03-15

Replace Column() function with there actual expressions assuming you will have the same dimension

Yes, please provide both arrow and final expression.

Arrow and final expression? Can you be a little more clearer with your description?

surajap123 · ‎2017-03-15

Thanks for clarifying. I am done with 1st requirement.

For the 2nd req- I need expression based on all 4 expression, that shows increase or decrease arrow compared to previous month.

sunny_talwar · ‎2017-03-15

Is the comparison done after selection of a month or are you comparing Max Month(March) with second max month (Feb)?

surajap123 · ‎2017-03-15

it is comparing max(month) with previous month, as you mentioned.

sunny_talwar · ‎2017-03-15

Did for one of them... see if this is what you wanted

surajap123 · ‎2017-03-15

Thanks!

I will get back after working on it.

surajap123 · ‎2017-03-15

Hi Sunny,

The 2ns requirement should show trend (up or down arrow) based on average of all the 3 expressions.(not for each expression as you wrote).

I tried using rangeavg(), but it doesn't hold set analysis.

help with expressions

Related Topics