# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted 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.

Tags (2)
1 Solution

Accepted Solutions MVP

## 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',

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

,'TBD'))

9 Replies MVP

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

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

## 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',

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

,'TBD'))

This is not working. Any ideas?

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. MVP

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

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

/

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

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

## 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',

/
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? MVP

## 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',