Class function for calculated values

[jessica_webb]

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!

[sunny_talwar]

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)

[avinashelite]

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

[jessica_webb]

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!

[jessica_webb]

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)

[sunny_talwar]

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

[jessica_webb]

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

[antoniotiman]

Like this ?

[sunny_talwar · Accepted solution]

May be try this

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

[jessica_webb]

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