Average calculation based on Max value

[deniscamh]

Hello all,

My data looks like this:

Load * Inline [

Employee, Date, Date_Hired, Year,Years_Of_Service

1,4/30/2017,4/18/2016, 2017,1.032

1,5/30/2017,4/18/2016, 2017,1.14

2,4/30/2017,7/25/2011, 2017,5.77

2,5/30/2017,7/25/2011, 2017,5.84];

What I am trying to create is a chart with time frames - Years, Quarter, Month, which calculates average Years_Of_Service per employee. So for year 2017 the calculation will be (1.14 + 5.84)/2.

I tried something like this: AVG(Agr(Max(Years_Of_Service),Employee)) but it does not work properly.

I know I am missing something small but cannot figure it out.

Please help.

Thanks

Denis

[sunny_talwar]

When you say it doesn't work, do you mean it gives null or no value? May be you need to add another g to Aggr() function name

Avg(Aggr(Max(Years_Of_Service), Employee))

[deniscamh]

It Gives wrong result.

I do have Aggr correct on my formula.

Thank you

[sunny_talwar]

You are not getting 3.49? Strange, but I am

[deniscamh]

Try to add years and you will see it does not work and create table wit Year as dimension and measure

AVG(Aggr(Max(Years_Of_Service),Employee))

Load * Inline [

Employee, Date, Date_Hired, Year,Years_Of_Service

1,4/30/2017,4/18/2016, 2017,1.032

1,5/30/2017,4/18/2016, 2017,1.14

1,5/30/2018,4/18/2016, 2018,2.2

2,4/30/2017,7/25/2011, 2017,5.77

2,5/30/2017,7/25/2011, 2017,5.84
2,4/1/2018,7/25/2011, 2018,6.69];

[sunny_talwar]

Well if year is your chart dimension, you would need that in your Aggr() function as well

Avg(Aggr(Max(Years_Of_Service), Employee, Year))

[deniscamh]

Yes I know that would help, but what I am looking for is an option to not to add dimensions into the formula as I might add more later, such as Month, Quarter or anything else. 

So I am looking for solution that use formula and the calculation is adjusted based on dimension on the chart:

For example I have a Bar chart with Year and Quarter as alternative dimension and the results should be reflected based on chosen dimension.

Hope it make sense. 

[sunny_talwar]

Well for your first part... I think if you are adding dimension to you table, then why not just add it to the expression at the same time... I don't think you can get around that....

But for your second issue (alternate dimension... this seems to be in Qlik Sense) you can try this expression

Avg(Aggr(Max(Years_Of_Service), Employee, $(='[' & GetObjectDimension() & ']')))

GetObjectDimension() will pick the dimension you are on based on the alternate dimension you are on.

[deniscamh]

Yes I just realized I posted it under Qlikview but I am actually using Sense.

Anyway this applies on both product.

So I tried to create a pivot table with Year and Quarter as dimensions and the measure as below:

Avg(Aggr(Max(Years_Of_Service), Employee, Year, Quarter)))

 So it gives correct result for Quarters but not for Year. So I guess the only option is build it with SecondaryDimensionality?

Also the 

$(='[' & GetObjectDimension() & ']'
did not work.
Thanks

[sunny_talwar]

---

@deniscamh wrote:

Also the 

$(='[' & GetObjectDimension() & ']'
did not work.
Thanks

---

You tried this in the pivot table or bar chart? GetObjectDimension() is used for alternate dimensions... did you have alternate dimensions in pivot table?

I guess you are not very clear with what you want? Are you not really sure what you want or do you have multiple things you want to accomplish and we are trying to solve 10 different things all at once?

1) GetObjectDimension() seems to work for me

Had to change the data to this

LOAD *,
	 'Q' & Ceil(Month(Date)/3) & '-' & Year as QuarterYear;
LOAD * INLINE [
    Employee, Date, Date_Hired, Year, Years_Of_Service
    1, 4/30/2017, 4/18/2016, 2017, 1.032
    1, 5/30/2017, 4/18/2016, 2017, 1.14
    1, 9/30/2018, 4/18/2016, 2018, 2.2
    2, 4/30/2017, 7/25/2011, 2017, 5.77
    2, 5/30/2017, 7/25/2011, 2017, 5.84
    2, 1/1/2018, 7/25/2011, 2018, 6.69
];

2) You are right, the pivot table might require Dimensionality()/SecondaryDimensionality() based on how the pivot table is set up.