Min and Max values of a "Class"

[paolo_mapelli]

Good afternoon,

     I've a table with one calculated dimension and one measure:

Calculated Dimension is defined as: class(aggr(Count(B02_Id),Customer),20,'Utilizzi')

Measure is defined as: Count(B02_Id)

Now, I'd like to pick min and max values for "Measure" but I'm going around by circles without finding a way to get them.

Can anyone help me ? Even if I suppose a simple answer...

Thanks in advance.

[MK_QSL]

What do you mean by min and max value for Measure?

What is the result for Min and Max as per your above example?

[paolo_mapelli]

Max: 98267

Min: 325

[#4]

How are you going to present the Min and Max values? I see what you are trying to get to, but I'm not really understanding the context.

[paolo_mapelli]

Hi Mike,

I don't understand why context is relevant... Anyway, let's suppose I want to display abovementioned Max and Min values into two different textboxes.

Thank you.

[MK_QSL]

It is something like

Max(Aggr(Count(B02_Id),YourDimension))

Min(Aggr(Count(B02_Id),YourDimension))

in this case, YourDimension is class(aggr(Count(B02_Id),Customer),20,'Utilizzi')

Which is a Calculated Dimension.

The problem is you can't use Calculated Dimension outside the object where it is declared.

So you have to create your Class in Script end.

If you provide sample data, we can provide further help.

[paolo_mapelli]

I tried creating class in script ("SET myClass = class(aggr(Count(B02_Id),Customer),20,'Utilizzi')") and referencing it with "$(myClass)" in calculated dimension definition but it doesn't work...

[MK_QSL · Accepted solution]

No this will not work...!

You have to use

Temp:

Load

     Customer,

     Count(B02_Id) as Total

Resident YourMainTable

Group By Customer;

Left Join (Temp)

Load

     Customer,

     If(Total >= 0 and Total < 20, Dual('0<= Utillizzi < 20',1),

          If(Total >= 20 and Total < 40, Dual('20<= Utillizzi < 40',2),

          If(Total >= 40 and Total < 60, Dual('20<= Utillizzi < 60',3),

          If(Total >= 60 and Total < 80, Dual('20<= Utillizzi < 80',4),

          If(Total >= 80 and Total < 100, Dual('20<= Utillizzi < 100',5),

          If(Total >= 100 and Total < 120, Dual('20<= Utillizzi < 120',6)))))))  as Flag

//Check number of brackets properly

Resident Temp;

Now either join this Temp table with main table or keep as it is.

Now use Flag as Dimension

[paolo_mapelli]

It works, but is (another) "dynamic" thing that Sense force you to design as "static"...

Very upset about that, mostly because "data are there" and you can't easily get them.

Thanks anyway.