Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Partner
Partner

Set analysis for average of range of sums

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.

Tags (2)
1 Solution

Accepted Solutions

Re: Set analysis for average of range of sums

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

9 Replies

Re: Set analysis for average of range of sums

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

robin_hausdoerfer
Valued Contributor III

Re: Set analysis for average of range of sums

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

     )

)

Partner
Partner

Re: Set analysis for average of range of sums

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?

robin_hausdoerfer
Valued Contributor III

Re: Set analysis for average of range of sums

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

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

Partner
Partner

Re: Set analysis for average of range of sums

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

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

Partner
Partner

Re: Set analysis for average of range of sums

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?

Re: Set analysis for average of range of sums

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