topic Re: Class() with different sizes in QlikView

Class() with different sizes

Wed, 13 Nov 2013 10:30:53 GMT

Hi all,

I want to make a chart with the sales amount divided in different classes.

When I use this expression in a calculated dimension, i have already a result:

=class(aggr(sum(Sales),SalesPerson),1000,'x')

But I don't want to divide in parts of 1000, I want to see this:

0 < x < 1.000

1.000 < x < 5.000

5.000 < x < 10.000

10.000 < x < 25.000

25.000 < x < 50.000

50.000 < x < 10.000

Is this possible in a calculated dimension?

I was thinking about working with predefined buckets, described in this blog: http://www.quickqlearqool.nl/?p=725.

But I don't think this is possible because the Sales can't be calculated in the script.

Thanks in advantage!

Fien

Re: Class() with different sizes

Tue, 26 Nov 2013 15:37:27 GMT

Any luck?

Cheers

Re: Class() with different sizes

Tue, 26 Nov 2013 15:42:04 GMT

No, I didn't find an answer yet.

Re: Class() with different sizes

Clever_Anjos — Tue, 26 Nov 2013 15:43:21 GMT

Please, continue this example

if(aggr(sum(Sales),SalesPerson) < 1000, '0< x<1000',

if(aggr(sum(Sales),SalesPerson)< 5000, '1000<x<5000',

if(aggr(sum(Sales),SalesPerson)< 10000, '5000<x<10000','>10000')))

Re: Class() with different sizes

jerem1234 — Tue, 26 Nov 2013 15:52:28 GMT

Class function can't do dynamic set widths.

The only way I can see doing it as a calculated dimension is a long if statement such as:

=if(aggr(sum(Sales),SalesPerson)<1, dual('0 < x < 1.000',0),if(aggr(sum(Sales),SalesPerson)<5, dual('1.000 < x < 5.000',5), if(aggr(sum(Sales),SalesPerson)<10, dual('5.000 < x < 10.000',10),if(aggr(sum(Sales),SalesPerson)<25, dual('10.000 < x < 25.000',25), if(aggr(sum(Sales),SalesPerson)<50, dual('25.000 < x < 50.000',50), dual('50.000 < x < 100.000',100))))))

The dual is for sorting the buckets.

Hope this helps!

Re: Class() with different sizes

Tue, 26 Nov 2013 16:15:36 GMT

Fien,

In fact you can try the following:

Load 1) your data as usual, 2) a grouping way (it will create the new dimension used to group) 3) do an intervalmatch that will affect each data to the group (here Units Sales). take care, The intervalmatch must be done like this.

Data:
LOAD Month,
Country,
Units
FROM[98796.xlsx](ooxml, embedded labels, table is Feuil1);

TableGroup:
LOAD Low,
High,
Group

FROM [98796.xlsx](ooxml, embedded labels, table is Feuil2);

Result:
IntervalMatch (Units)
LOAD Low, High

Resident TableGroup;

Attached the files that I used.

Fabrice