topic Growth Calculation Expression in QlikView

Growth Calculation Expression

kakani87 — Mon, 09 Jul 2018 10:00:21 GMT

The below expression considered for growth calculation in the report

(sum(sale_amount)/Before(sum({<Yr>}sale_amount))-1)

And

(sum(sale_amount)/Before(sum({<Yr>}sale_amount))-1)*avg(1)

Can some one tell me why to consider *avg(1)

Where avg(1) returns 1

2. if we give in set expression as {<Yr=>} instead {<Yr>} both does the same ??? to not get filtered year selection

* My Intention behind this is to understand this expression clearly

-Hope someone can help me with this

Re: Growth Calculation Expression

kfoudhaily — Mon, 09 Jul 2018 10:10:09 GMT

{<Yr=>} is the same as {<Yr>}

for avg(1) i'm not sure.

regards,

Re: Growth Calculation Expression

kakani87 — Mon, 09 Jul 2018 10:48:25 GMT

Can Some one help me to understand this

mbaeyens‌

tresesco‌

Re: Growth Calculation Expression

Miguel_Angel_Baeyens — Mon, 09 Jul 2018 11:02:15 GMT

For the Avg(1) I don't know why do you have to use it, or if it is a copy-paste from another application. It does not make sense to me, unless "1" is actually a field in the data model, in which case I would instead use [1] or "1" in

the expressions.

For the set analysis syntax, kh fou replied above.

Re: Growth Calculation Expression

tresB — Mon, 09 Jul 2018 11:28:30 GMT

Usually this sort of vague aggregation is used along with any set analysis expression, to avoid unwanted dimension members that could come because of set analysis selection ignorance. That is, in your case, since you have used {<Yr>} to ignore the effect of this fields selection, if you have the same field in the dimension, you could see members that are not intended (beyond selection). If you add up additional aggregate function (like avg(1)) without set analysis, such repercussion could be avoided. Not sure, if that is the case with your chart or not.

Re: Growth Calculation Expression

sunny_talwar — Mon, 09 Jul 2018 11:29:04 GMT

I agree with mbaeyens‌ and I don't think Avg(1) is needed here. But in general it is used in order to force an expression to be 0 for out of selection value which still honoring the Above/Below/After/Before conditions. So, in your case you are ignoring Yr field within the Before() which is telling the expression to look at 2017 value even if you have selected 2018. If this was your only selection Before(sum({<Yr>}sale_amount)) you would find that even though you have selected 2018, you are seeing all the years. In order to make unselected years 0, you can multiply with Avg(1) because Avg(1) = 0 for anything out of selection and Avg(1) = 1 for selections.

Does this make sense?

Re: Growth Calculation Expression

kakani87 — Mon, 09 Jul 2018 12:02:10 GMT

Here in the below screen it is shown as exp with * avg(1) and with out considering

then on dynamic filter of year data looks like this

My question is how to express this technically, but I'm clear about what it dose

Re: Growth Calculation Expression

kakani87 — Mon, 09 Jul 2018 12:20:20 GMT

Happy to hear from you people

stalwar1

tresesco

Actually trying to understand this from your stalwar1 previous thread answered to some other mates , one thread among them.

https://community.qlik.com/thread/270791‌

Regards,

Kakani.

Re: Growth Calculation Expression

kfoudhaily — Mon, 09 Jul 2018 12:23:26 GMT

the answer might also be in your data, when testing the two expressions do you detect any differences?

Re: Growth Calculation Expression

kakani87 — Mon, 09 Jul 2018 12:28:10 GMT

No ... the o/p is similar for both the expressions, but on dynamic filter the display of chart varies

Now I'm Clear with this as Sunny explained very clearly what happens

Re: Growth Calculation Expression

sunny_talwar — Mon, 09 Jul 2018 12:31:41 GMT

Super