Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

paolo_mapelli
Contributor

Min and Max values of a "Class"

Good afternoon,

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

Clipboard02.jpg

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.

1 Solution

Accepted Solutions

Re: Min and Max values of a "Class"

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


8 Replies

Re: Min and Max values of a "Class"

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
Contributor

Re: Min and Max values of a "Class"

Max: 98267

Min: 325

Not applicable

Re: Min and Max values of a "Class"

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
Contributor

Re: Min and Max values of a "Class"

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.

Re: Min and Max values of a "Class"

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
Contributor

Re: Min and Max values of a "Class"

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

Re: Min and Max values of a "Class"

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
Contributor

Re: Min and Max values of a "Class"

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.

Community Browser