topic Re: Mathematical formula using Valueloop in QlikView

Mathematical formula using Valueloop

Tue, 25 Oct 2016 09:06:25 GMT

Hello All,

I am having an issue with a formula (Please view attached qvw sample)

This is my formula:

=sum(pow([Intensity],Valueloop(0,[Agents]-1))/fact(Valueloop(0,10)))

In the expression tab it says that the Intensity column in my table does not exist but it does exist as per illustrated in my qvw.

However when I replace the Column [Intensity] by a number, lets say 5 the formula seems to be working.

Any idea on to how to resolve this problem.

Many Thanks,

Hasvine

Re: Mathematical formula using Valueloop

swuehl — Tue, 25 Oct 2016 09:20:27 GMT

I don't think you can use a non-constant field value like Agents as argument to Valueloop().

edit: Not quite sure what calculation you want to do using the Valueloop() method, and I don't really know if it's a safe thing if you are using more than one valueloop() with different ranges in the same aggregation function.

Besides this, the calculation is performed as long as you select an agent:

=sum(pow([Intensity],Valueloop(0,[Agents]-1)))/ Sum(fact(Valueloop(0,10)))

Re: Mathematical formula using Valueloop

Wed, 26 Oct 2016 11:15:36 GMT

Hello Stefan,

Thanks for the help. Actually my real expression to calculate Intensity and Agents are long.

Intensity:

((Count(ID)/Count(DISTINCT(Date)))*(Interval((Sum({<FirstAgent={'<>0'}>}conv) + (Sum({<FirstAgent={'<>0'}>} wrap)))/ Count({<FirstAgent={'<>0'}>}ID),'ss')*86400)//vP)

Agents:

(Count(DISTINCT {<FirstAgent={'<>0'}>}(Agent))/Count(DISTINCT(Date)))

What I did on the sample was just to understand why the expression "=sum(pow([Intensity],Valueloop(0,[Agents]-1))/fact(Valueloop(0,10)))" was not functioning.

I am trying to break the formula down to identify the problem but it is still not working:

For example:

I replaced the [Intensity] by the expression to calculate the Intensity but still no luck:

sum(pow(((Count(ID)/Count(DISTINCT(Date)))*(Interval((Sum({<FirstAgent={'<>0'}>}conv) + (Sum({<FirstAgent={'<>0'}>} wrap)))/ Count({<FirstAgent={'<>0'}>}ID),'ss')*86400)/vP),Valueloop(0,1)))

My Original formula is as follows and the part highlighted in yellow is what I am unable to do on qlikview.

Any idea? Is it possible to perform on qlikview.

Is there a kind of technical support who can help me on this if I cannot find the answer on the qlikview community?

Many Thanks & Kind Regards,

Hasvine

Re: Mathematical formula using Valueloop

swuehl — Wed, 26 Oct 2016 11:46:53 GMT

You are embedding aggregation functions inside other aggregation functions, which is only possible when using advanced aggregation (aggr() function) - or the TOTAL qualifier.

Besides this:

- Do you need to be able to evaluate the expression in the context of your chart dimensions, i.e. are m, u parameters depending on your chart dimensions?

- If so, then I think you will run into troubles using the Valueloop() method, since Valueloop() needs constant parameter in the scope of your chart expression evaluation

- Are you maybe able to calculate your formula in the script, or do it need to be sensitive to selections?

You can contact Qlik or a Qlik partner to ask for (probably paid) technical support. You can find contact addresses in your region on the Qlik.com web site.

Good luck,

Stefan