topic Re: Class function for calculated values in QlikView

Class function for calculated values

jessica_webb — Wed, 25 Nov 2020 16:16:04 GMT

I am creating several histograms using the class function.

Where the histogram is for a straightforward value that is being pulled from my data, I'm not having any issues.

For example

Note that 'BinWidth' is a variable which is defined by an input box

DIM:

=CLASS(AGGR(AVG({<GEN={'ALL'}>}AP_Weighted),ID),BinWidth)

EXP:

Count(DISTINCT ID)

However, I'm now trying to create a histogram with a much more complex value and here I'm coming unstuck!

The expression on its own (which works fine in a table) is:

AVG({<GEN={'ALL'}>}PriorAP)-AGGR(NODISTINCT AVG({<GEN={'ALL'}>} PriorAP),ID)

But when I try to insert that into the calculated dimension like this:

=CLASS(AGGR(AVG({<GEN={'ALL'}>}PriorAP)-AGGR(NODISTINCT AVG({<GEN={'ALL'}>} PriorAP),ID),ID),BinWidth)

I get complete nonsense!

Re: Class function for calculated values

sunny_talwar — Wed, 28 Jun 2017 13:43:53 GMT

You love Nodistinct, don't you? May be try this

=Class(Aggr(Avg({<GEN={'ALL'}>}PriorAP)-Avg(TOTAL <ID> {<GEN={'ALL'}>} PriorAP), ID, KS5_LAESTAB), BinWidth)

Re: Class function for calculated values

avinashelite — Wed, 28 Jun 2017 13:47:31 GMT

Add KS5_LAESTAB as the Dimension you will get the result ...

In order to use AGGR you need to have that dimension in the chart

Re: Class function for calculated values

jessica_webb — Wed, 28 Jun 2017 14:20:51 GMT

Love it... Or just blindly add it to my expressions!!

I'm afraid your suggestion didn't work. But that's my fault as I had given my example incorrectly!

Have amended it above, but to clarify I was trying to use:

=CLASS(AGGR(AVG({<GEN={'ALL'}>}PriorAP)-AGGR(NODISTINCT AVG({<GEN={'ALL'}>} PriorAP),ID),ID),BinWidth)

So I tried your suggestion amended to:

=Class(Aggr(Avg({<GEN={'ALL'}>}PriorAP)-Avg(TOTAL <ID> {<GEN={'ALL'}>} PriorAP), ID, ID), BinWidth)

But this gives me nothing in my chart at all!

Re: Class function for calculated values

jessica_webb — Wed, 28 Jun 2017 14:21:45 GMT

Sorry Avinash, I gave the wrong example!

I'm actually trying to use:

=CLASS(AGGR(AVG({<GEN={'ALL'}>}PriorAP)-AGGR(NODISTINCT AVG({<GEN={'ALL'}>} PriorAP),ID),ID),BinWidth)

Re: Class function for calculated values

sunny_talwar — Wed, 28 Jun 2017 15:57:03 GMT

Would you be able to share few rows of data to show what exactly are you trying to do here?

Re: Class function for calculated values

jessica_webb — Thu, 29 Jun 2017 07:38:45 GMT

Hi Sunny,

I've attached a spreadsheet of the table that this histogram should build upon.

You'll see there's a column called 'PriorAP' which is created with the expression:

=AVG({<GEN={'ALL'}>}PriorAP)-AGGR(NODISTINCT AVG({<GEN={'ALL'}>} PriorAP),ID)

What I would want to see in the Histogram is essentially the number of rows that fall into each bin. I've realised now actually that referring to 'ID' in my expression perhaps doesn't make sense, as I'm not really interested in distinct or non-distinct IDs...

So for example, with the attached data, if I set my bin width to 0.5, I would expect to see frequencies approximately of:

Bin	Frequency
-12.5	1
-12
-11.5
-11
-10.5
-10
-9.5
-9
-8.5
-8
-7.5	1
-7
-6.5
-6	2
-5.5	2
-5	1
-4.5	1
-4
-3.5	4
-3	2
-2.5	5
-2	4
-1.5	1
-1	12
-0.5	7
0
0.5	21
1	9
1.5	6
2	3
2.5	4
3	2
3.5
4
4.5	1
5

Re: Class function for calculated values

antoniotiman — Thu, 29 Jun 2017 07:59:37 GMT

Like this ?

Re: Class function for calculated values

sunny_talwar — Thu, 29 Jun 2017 09:00:55 GMT

May be try this

=Aggr(Avg({<GEN={'ALL'}>}PriorAP)-Avg(TOTAL <ID>{<GEN={'ALL'}>} PriorAP),ID, Type, Set)

Re: Class function for calculated values

jessica_webb — Thu, 29 Jun 2017 12:36:57 GMT

Hi Sunny,

After a lot of testing, I think that's working..!

So the full expression I'm using is:

=Class(Aggr(Avg({<GEN={'ALL'}>}PriorAP)-Avg(TOTAL <ID>{<GEN={'ALL'}>} PriorAP),ID, Type, Set), BinWidth)

Thanks once again for your help

Jess

Re: Class function for calculated values

jessica_webb — Wed, 12 Jul 2017 11:29:20 GMT

Hi Sunny,

Hope you don't mind me bringing this one up again!!!

The last expression you suggested is working perfectly, but I now have an even more complex one that I'm struggling to get into my histogram!!!

As mentioned before, the expression that worked before is:

=Class(Aggr(Avg({<GEN={'ALL'}>}PriorAP)-Avg(TOTAL <ID>{<GEN={'ALL'}>} PriorAP),ID, Type, Set), BinWidth)

I am now trying to edit it to get the following expression (which works in my straight table) into the histogram:

=SUM({1<GEN={'ALL'}>}NUM)/AGGR(NODISTINCT SUM({1<GEN={'ALL'}>} NUM),ID)/AVG({1}TOTAL AGGR(SUM({1<GEN={'ALL'}>}NUM)/SUM(TOTAL <ID> {1<GEN={'ALL'}>} NUM), ID, Type_Set))

What I've tried is:

=Class(Aggr

(Sum({<GEN={'ALL'}>}NUM)/SUM({1}TOTAL <ID>{1<GEN={'ALL'}>} NUM)/

(AVG({1}TOTAL AGGR(SUM({1<GEN={'ALL'>}NUM)/SUM(TOTAL <ID> <Type_Set> {1<GEN={'ALL'}>}NUM)

,ID, Type, Set), BinWidth)

But wow... what a mess!!! Any ideas?