Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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'))
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))
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
)
)
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?
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.
Since there are multiple things going on here, what would you want your total to show?
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 "-"
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
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?
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'))