topic Re: help with expressions in QlikView

help with expressions

surajap123 — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: help with expressions

sunny_talwar — Wed, 15 Mar 2017 11:15:20 GMT

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?

Re: help with expressions

surajap123 — Wed, 15 Mar 2017 11:22:01 GMT

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.

Re: help with expressions

sunny_talwar — Wed, 15 Mar 2017 11:33:40 GMT

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?

Re: help with expressions

surajap123 — Wed, 15 Mar 2017 11:50:26 GMT

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.

Re: help with expressions

sunny_talwar — Wed, 15 Mar 2017 11:53:49 GMT

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

Re: help with expressions

surajap123 — Wed, 15 Mar 2017 12:02:35 GMT

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

Re: help with expressions

sunny_talwar — Wed, 15 Mar 2017 12:12:49 GMT

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

Re: help with expressions

surajap123 — Wed, 15 Mar 2017 12:29:43 GMT

Thanks!

I will get back after working on it.

Re: help with expressions

surajap123 — Wed, 15 Mar 2017 16:02:26 GMT

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.

Re: help with expressions

sunny_talwar — Wed, 15 Mar 2017 16:08:51 GMT

Can you share the exact expression you tried?

Re: help with expressions

surajap123 — Wed, 15 Mar 2017 17:02:12 GMT

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

Re: help with expressions

sunny_talwar — Wed, 15 Mar 2017 17:05:50 GMT

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

Re: help with expressions

surajap123 — Thu, 16 Mar 2017 11:43:58 GMT

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!

Re: help with expressions

sunny_talwar — Thu, 16 Mar 2017 11:51:48 GMT

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

Re: help with expressions

surajap123 — Thu, 16 Mar 2017 12:02:54 GMT

Thanks for your suggestion Sunny. I totally agree. !

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

Re: help with expressions

sunny_talwar — Thu, 16 Mar 2017 12:05:31 GMT

Check the attached

Re: help with expressions

surajap123 — Thu, 16 Mar 2017 12:43:58 GMT

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!

Re: help with expressions

sunny_talwar — Thu, 16 Mar 2017 12:49:31 GMT

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

Re: help with expressions

surajap123 — Thu, 16 Mar 2017 13:51:49 GMT

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