topic Re: Avg of aggregated function in QlikView

Avg of aggregated function

Wed, 15 Apr 2015 09:55:06 GMT

Hi,

I need to show the average of the below function.

For instance calculating the average for three days.

Any ideas?

//O

Sum({ ({<SoD_Site= {'XXX'}>} Aggr(IF(SoD_SettlementOrder= (MAX({ <SoD_Site= {'XXX'}>} TOTAL <Date> SoD_SettlementOrder)), Sum({ <SoD_Site= {'XXX'}>} SoD_RunningBalance)), Date, SoD_SettlementOrder))

Re: Avg of aggregated function

marcus_sommer — Wed, 15 Apr 2015 11:30:54 GMT

This looked very weird. I think the expression should more look like:

avg(aggr(sum({< Condition = {'xxx'}>} value), Dimension))

Re: Avg of aggregated function

Wed, 15 Apr 2015 12:21:29 GMT

It does look a bit weird I agree, but It also gives me the expected values.

In your example above there is only one condition, but I have two.

This is what it shows.

And now I need the AVG of the below function.

I tried but could not make it work

-------AVG OF---------

For the chosen Date

For a perticular SoD_Site ('XXX')

Show the SoD_RunningBalance corresponding to the MAX(SettlementOrder)

Re: Avg of aggregated function

marcus_sommer — Wed, 15 Apr 2015 12:49:40 GMT

You could put more conditions as one in your expression, maybe:

avg(aggr(sum({< Condition1 = {'xxx'}, Condition2 = {"$(=max(SettlementOrder))"}>} value), Dimension))

Often it's easier to split complex expressions into several parts to see which one worked (expected results) and then put them together again.

- Marcus

Re: Avg of aggregated function

Mon, 27 Apr 2015 14:46:18 GMT

Hi Marcus,

I tried the below however the second condition does not work it seems like. Do you have a clue what I might be doing wrong?

(aggr(sum(

{< SoD_Site = {'EEUHEE2XXXX'}, SoD_SettlementOrder = {"$(=max(SoD_SettlementOrder))"}>}

SoD_RunningBalance),

Date))

Re: Avg of aggregated function

marcus_sommer — Mon, 27 Apr 2015 14:57:09 GMT

You need to make sure that SoD_SettlementOrder is numeric and then you should have in mind that "$(=max(SoD_SettlementOrder))" will create a variable which could only have one value at a time and which will be calculated once for the whole chart and applied for each calculation on row-level.

- Marcus