topic Re: Average of expression in QlikView

Average of expression

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

Hi ,

How to calculate the average for the below expression.

Logic : num(Sum({$<A7AIDT = {"$(=vYesterday)"}>} ESIGN_COMPLETE_NEW)/Sum({$<A7AIDT = {"$(=vYesterday)"}>} TOTAL_ORDERS_DAILY_NEW),'##.00')*100)

Thanks...

Re: Average of expression

Jason_Michaelides — Thu, 08 Sep 2016 09:29:35 GMT

I assume you mean you want to calculate the average of this expression over a given dimension? In that case try this:

Avg(

Aggr(

Sum({$<A7AIDT = {"$(=vYesterday)"}>} ESIGN_COMPLETE_NEW)/Sum({$<A7AIDT = {"$(=vYesterday)"}>} TOTAL_ORDERS_DAILY_NEW)

,<YourDimension>)

)

Hope this helps,

Jason

Re: Average of expression

Anil_Babu_Samineni — Thu, 08 Sep 2016 09:29:41 GMT

Try with single quote. And usually average is

Sum(Sales)/Count(TOTAL Sales)

num(Sum({$<A7AIDT = {'$(=vYesterday)'}>} ESIGN_COMPLETE_NEW)/Sum({$<A7AIDT = {'$(=vYesterday)'}>} TOTAL_ORDERS_DAILY_NEW),'##.00')*100)

Re: Average of expression

nareshthavidishetty — Thu, 08 Sep 2016 10:10:53 GMT

Hi,

It's showing the same percentage results.

Thanks..

Re: Average of expression

sunny_talwar — Thu, 08 Sep 2016 10:16:54 GMT

Would you be able to share more details as to what you are looking to get?

Re: Average of expression

nareshthavidishetty — Thu, 08 Sep 2016 10:27:23 GMT

Hi,

We have calculating the sales percentage based on the below,

Logic : num(Sum({$<A7AIDT = {"$(=vYesterday)"}>} ESIGN_COMPLETE_NEW)/Sum({$<A7AIDT = {"$(=vYesterday)"}>} TOTAL_ORDERS_DAILY_NEW),'##.00')*100)

We are getting the results as below,

But my user needs the average of these percentages(top of the bar),in an line chart as well..

Thanks..

Re: Average of expression

sunny_talwar — Thu, 08 Sep 2016 10:28:55 GMT

Have you tried adding a Avg trendline?

Re: Average of expression

Jason_Michaelides — Thu, 08 Sep 2016 10:37:00 GMT

Did you try my Aggr() solution?

Re: Average of expression

nareshthavidishetty — Thu, 08 Sep 2016 10:37:19 GMT

yes, i have tried but it's showing an straight line.

I need to show an trend line.

Re: Average of expression

Jason_Michaelides — Thu, 08 Sep 2016 10:42:18 GMT

Ah, then as Sunny T says, just add a trendline in the chart Expression tab.

Re: Average of expression

sunny_talwar — Thu, 08 Sep 2016 12:33:15 GMT

What all are your dimensions here?