Growth Calculation Expression

[kakani87]

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)

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

[kfoudhaily]

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

for avg(1) i'm not sure.

regards,

[kakani87]

Can Some one help me to understand this

mbaeyens‌

tresesco‌

[Miguel_Angel_Baeyens]

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.

[tresesco]

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.   

[sunny_talwar · Accepted solution]

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?

[kakani87]

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

[kakani87]

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.

[kfoudhaily]

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

[kakani87]

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