topic Re: Set analysis for average of range of sums in QlikView

Set analysis for average of range of sums

jeckstein — Tue, 08 Nov 2016 18:14:44 GMT

I have a equation with set analysis that looks like this.

Sum({<Rolling7Day={1},Metric_Category={'Expense Items'},Metric={'Tonight'},FutureFlag={0} >}(LOS_Numerator))
/
Sum({<Rolling7Day={1},Metric_Category={'Expense Items'},Metric={'Tonight'},FutureFlag={0} >}(LOS_Denominator))

When put into a table (Date as dimension) and with the parts broken out I get an output like this.

Date	Total	Numerator	Denominator
	3.982403218	7921	1989
11/1/2016	3.946859903	817	207
11/2/2016	4.006024096	665	166
11/3/2016	4.004854369	825	206
11/4/2016	3.68372093	792	215
11/5/2016	3.16025641	493	156
11/6/2016	3.13559322	370	118
11/7/2016	4.298588491	3959	921
TOTAL	26.23589742	7921	1989

The expression total is 3.98 (7921/1989)

My issue is I ned the Expression total to be 3.74. This is the average of the Total Column.

My desired result when putting the Equation above into a text box is to get 3.74 the average of (LOS Numerator/ LOS Denominator) for each specific day.

Thanks in advance.

Re: Set analysis for average of range of sums

sunny_talwar — Tue, 08 Nov 2016 18:21:49 GMT

Try this:

Avg(Aggr(

Sum({<Rolling7Day={1},Metric_Category={'Expense Items'},Metric={'Tonight'},FutureFlag={0} >}(LOS_Numerator))

Sum({<Rolling7Day={1},Metric_Category={'Expense Items'},Metric={'Tonight'},FutureFlag={0} >}(LOS_Denominator))

, Date)

Basically, you need to use an aggr() function here:

Avg(Aggr(YourExpression, YourDimesnion))

Re: Set analysis for average of range of sums

Anonymous — Tue, 08 Nov 2016 18:22:08 GMT

avg(

aggr(

          Sum({<Rolling7Day={1},Metric_Category={'Expense Items'},Metric={'Tonight'},FutureFlag={0} >}(LOS_Numerator))
          /
          Sum({<Rolling7Day={1},Metric_Category={'Expense Items'},Metric={'Tonight'},FutureFlag={0} }(LOS_Denominator))

Date

)

Re: Set analysis for average of range of sums

jeckstein — Tue, 08 Nov 2016 20:11:32 GMT

This is working but, if i use a If statement before it like this

IF(Metric='Tonight',
Num(Avg(Aggr(
Sum({<Rolling7Day={1},Metric_Category={'Expense Items'},Metric={'Tonight'},FutureFlag={0} >}(LOS_Numerator))
/
Sum({<Rolling7Day={1},Metric_Category={'Expense Items'},Metric={'Tonight'},FutureFlag={0} >}(LOS_Denominator))
, Date)),'#.##'))

IF(Metric='Tomorrow',

NUM(Avg(Aggr((Sum({<PreviousDay={1},Metric_Category={'Expense Items'},Metric={'Tomorrow'} >}(FTE_ADC_Hours_Numerator))/8)
/
Sum({<PreviousDay={1},Metric_Category={'Expense Items'},Metric={'Tomorrow'} >}(FTE_ADC_Denominator)), Date)),'#,##0')

,'TBD'))

This is not working. Any ideas?

Re: Set analysis for average of range of sums

Anonymous — Tue, 08 Nov 2016 20:40:17 GMT

if( ... sum( ...))

is a bad idea. If you have more than one return value within your dimension hierarchy, you'll get null() as return value.

Re: Set analysis for average of range of sums

sunny_talwar — Tue, 08 Nov 2016 20:44:50 GMT

Since there are multiple things going on here, what would you want your total to show?

Re: Set analysis for average of range of sums

jeckstein — Tue, 08 Nov 2016 20:52:01 GMT

So in this example. I made a straight table with the dimension Metric.

For metric Tonight the output of the following

Num(Avg(Aggr(

Sum({<Rolling7Day={1},Metric_Category={'Expense Items'},Metric={'Tonight'},FutureFlag={0} >}(LOS_Numerator))

Sum({<Rolling7Day={1},Metric_Category={'Expense Items'},Metric={'Tonight'},FutureFlag={0} >}(LOS_Denominator))

, Date)),'#.##'))

For metric Tomorrow the output of the following

NUM(Avg(Aggr((Sum({<PreviousDay={1},Metric_Category={'Expense Items'},Metric={'Tomorrow'} >}(FTE_ADC_Hours_Numerator))/8)

Sum({<PreviousDay={1},Metric_Category={'Expense Items'},Metric={'Tomorrow'} >}(FTE_ADC_Denominator)), Date)),'#,##0')

These two expression work perfectly, but if and "IF Statement" is added they return of "-"

Re: Set analysis for average of range of sums

sunny_talwar — Tue, 08 Nov 2016 22:36:12 GMT

Can you show the snapshot to see the place where you are seeing null? I want to understand the structure of your table before I answer this

Re: Set analysis for average of range of sums

jeckstein — Tue, 08 Nov 2016 23:20:26 GMT

Straight Table:

Metric	Actual
Tonight	-
Tomorrow	-

Metric

Actual

Tonight

Tomorrow

The Expression for Actual is =

IF(Metric='Tomorrow',

,'TBD'))

and I am getting a result of null in the Actual column.

Does this work?

Re: Set analysis for average of range of sums

sunny_talwar — Tue, 08 Nov 2016 23:22:18 GMT

Got it, try adding NODISTINCT within Aggr() function:

IF(Metric='Tonight',
Num(Avg(Aggr( NODISTINCT
Sum({<Rolling7Day={1},Metric_Category={'Expense Items'},Metric={'Tonight'},FutureFlag={0} >}(LOS_Numerator))
/
Sum({<Rolling7Day={1},Metric_Category={'Expense Items'},Metric={'Tonight'},FutureFlag={0} >}(LOS_Denominator))
, Date)),'#.##'))

IF(Metric='Tomorrow',

NUM(Avg(Aggr(NODISTINCT(Sum({<PreviousDay={1},Metric_Category={'Expense Items'},Metric={'Tomorrow'} >}(FTE_ADC_Hours_Numerator))/8)
/
Sum({<PreviousDay={1},Metric_Category={'Expense Items'},Metric={'Tomorrow'} >}(FTE_ADC_Denominator)), Date)),'#,##0')

,'TBD'))