topic Re: Average calculation based on Max value in QlikView

Average calculation based on Max value

deniscamh — Thu, 29 Nov 2018 17:09:52 GMT

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

Re: Average calculation based on Max value

sunny_talwar — Thu, 29 Nov 2018 17:13:51 GMT

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

Re: Average calculation based on Max value

deniscamh — Thu, 29 Nov 2018 17:17:54 GMT

It Gives wrong result.

I do have Aggr correct on my formula.

Thank you

Re: Average calculation based on Max value

sunny_talwar — Thu, 29 Nov 2018 17:21:59 GMT

You are not getting 3.49? Strange, but I am

Re: Average calculation based on Max value

deniscamh — Thu, 29 Nov 2018 18:05:38 GMT

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];

Re: Average calculation based on Max value

sunny_talwar — Thu, 29 Nov 2018 18:17:40 GMT

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

Re: Average calculation based on Max value

deniscamh — Thu, 29 Nov 2018 18:24:31 GMT

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.

Re: Average calculation based on Max value

sunny_talwar — Thu, 29 Nov 2018 18:40:23 GMT

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.

Re: Average calculation based on Max value

deniscamh — Thu, 29 Nov 2018 18:57:35 GMT

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

Re: Average calculation based on Max value

sunny_talwar — Thu, 29 Nov 2018 19:02:35 GMT

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

Re: Average calculation based on Max value

deniscamh — Thu, 29 Nov 2018 19:10:09 GMT

Yes I tired the $(='[' & GetObjectDimension() & ']' on Bar chart, thank you for clarifying. But it does not work. I will try to play with it later.

Regarding to the Dimensionality I was hoping to avoid that.

Thanks anyway.

Re: Average calculation based on Max value

sunny_talwar — Thu, 29 Nov 2018 19:14:49 GMT

Oh, you know what... GetObjectDimension() was recently added to Qlik Sense. Are you using an older version of Qlik Sense? Older than September 2017?

Qlik Sense September 2017 November 2017 Releases

Re: Average calculation based on Max value

deniscamh — Thu, 29 Nov 2018 19:18:18 GMT

No I am using Sep 2018 version now.

Re: Average calculation based on Max value

sunny_talwar — Thu, 29 Nov 2018 19:26:30 GMT

No idea my friend. I wish I had the option to post the qvf where it is working for me.

Re: Average calculation based on Max value

deniscamh — Thu, 29 Nov 2018 19:29:26 GMT

That is ok I will try to figure out.

may be I am doing something wrong.

Thanks for your help.

Re: Average calculation based on Max value

sunny_talwar — Thu, 29 Nov 2018 19:35:39 GMT

Sounds good. All the best for your project!!