Set analysis for average of range of sums

[jeckstein]

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.

[sunny_talwar]

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))

[Anonymous]

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

     )

)

[jeckstein]

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?

[Anonymous]

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.

[sunny_talwar]

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

[jeckstein]

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 "-"

[sunny_talwar]

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

[jeckstein]

Straight Table:

Metric	Actual
Tonight	-
Tomorrow	-

The Expression for Actual is =

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'))

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

Does this work?

[sunny_talwar · Accepted solution]

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'))