Find Minimum on a measure calculated on average of aggr function

[iperintelligence]

Hi!

I am working on this data model.

I have a table with one dimension (PROD_CD) and a measure calculated as this:

• avg(aggr(sum(ArtScontrPrezzoTot),PROD_CD,ENTE_CD,AnnoMicrosettimana))

I want to normalize this measure to create an index.

So, i have to find min and max on the column of the calculated measure.

I tried with these code lines:

• min(Total avg(aggr(sum(ArtScontrPrezzoTot),PROD_CD,ENTE_CD,AnnoMicrosettimana)))

but QlikSense shows this error: ‘Error in expression: nested aggregation not allowed’

How can i find the minimum and maximum on average of aggr function?

Thank you

[William3]

Has any got any suggestions on this question.

I also would like to NORMALIZE a set of values but as stated above you need the average min and max in a formula like this:

(Avg(Value) - Min(Avg(Value))) / (Max(Avg(Value)) - Min(Avg(Value)))

I've tried using Aggr multiple ways but still is doesn't work. If someone could just come up with a equation structure maybe I could take it from there.

[Digvijay_Singh]

Your average function is returning a single value from this - 

• avg(aggr(sum(ArtScontrPrezzoTot),PROD_CD,ENTE_CD,AnnoMicrosettimana))

You need to introduce another dimension if you want to calculate average against any new dimension, after that only you can calculate min or max of multiple average values against that new dimension.

It has to be something like this - 

Min(Aggr(avg(aggr(sum(ArtScontrPrezzoTot),PROD_CD,ENTE_CD,AnnoMicrosettimana)), Newdimension))

[William3]

Thank you Digvijay for your quick response but unfortunately all I get is NULLS, so maybe I'm doing it wrong. Below are different formula's I've tried with no success:

Max(Aggr(avg(aggr(sum(Value), Dim1, Time), Dim1, Time), Dim1, Time)) 

Max(Aggr(Avg(Value), Dim1, Time)) - returns incorrect numbers

Aggr(Max(Aggr(Avg(Value), Dim1, Time)), Dim1)

Dim1 has hundreds of values across multiple years, Time is year and month.

Unfortunately I can not upload an example because the data is proprietary.

[Digvijay_Singh]

Not sure but to troubleshoot, create a sample straight table having - 

Dimensions - Dim1, Time

Measures -  sum(Value), avg(aggr(sum(Value), Dim1, Time), Max(Aggr(avg(aggr(sum(Value), Dim1, Time), Dim1, Time), Dim1, Time)) 

Then Filter your data to handful of dim values and try to understand where things are getting wrong..

[William3]

The included chart sample is what I'm trying to accomplish, for each line (Dimension) there should be at least 1 value that reaches 1 and at least 1 value that reaches 0 with all other values falling in-between.

I have included a spreadsheet that shows a subset of the data I'm working with, below is a sample of what I understand as the formula to Normalize data. I just can't figure out how to accomplish this in Qlik:

Round((Avg(VALUE) - Min(Avg(VALUE))) / (Max(Avg(VALUE)) - Min(Avg(VALUE))), 0.001)

I appreciate any help you can give me.

[Digvijay_Singh]

I still don't understand how are you calculating averages, I see just one value for each time period and Dim value combination.

I tried to represent your data in a table in the attached app, See if you can utilize some of the formulas, I know they are not yet in the shape you want but just give a shot if they are of any use..

[William3]

My apology's, in my haste I mistyped and summed the numbers. In the script the values are grouped by Parent Location, Child Location, Customer and Product. The user has the option to select any of these to use as filters on the sheet.

I have also looked at the Qlik functions 'NormInv' and 'NormDist'. 'NormDist' gives me close to the same results I've already got and I can't figure out 'NormInv'.

My Boss has taken these same numbers and in a Excel Spreadsheet shown how it should look with all the values falling between 0 and 1. Not sure why this is so difficult in Qlik.

I have included a new spreadsheet with the numbers not summed.

I will take a look at your app.

Thank you for your patience and help.