topic Re: Aggr ( Aggr + Aggr) in QlikView

Aggr ( Aggr + Aggr)

Fri, 24 Feb 2012 15:46:10 GMT

Hi,

My client wants to visualize the count of customers in certain classifications and give each 'bin' a color.

For example we have for class A

1 > 200 green

2 > 100 yellow

3 > 50 orange

4 <= 50 red

This is easy if I precalculate the bins

The problem I have is that the client has combined 2 of these classifications (A + B) / 2 = C .

We have for class B

1 > 1000 green

2 > 800 yellow

3 > 50 orange

4 <= 50 red

Class C =

(Class A + Class B) / 2

Any Idea how I could implement this in Qlikview.

I've included an example to play with (Data of cours scrambled)

Can someone help constructing one of these charts?

Kind Regards

Re: Aggr ( Aggr + Aggr)

swuehl — Fri, 24 Feb 2012 16:36:13 GMT

Maybe like attached?

I moved the score calculation from the expressions into calculated dimensions, so I only have one dimension that will generate my QualityScore.

The expression is then just a count of Supplier names.

I reused the dimension calculation also for the background color attribute expression, but didn't spend much time on the colors, so just state the correct colors in the pick() function.

It's probably not very performant on large data volumes, so if you don't need to be selection sensitive, you can maybe do the classification stuff all in the script.

And maybe I missed something better anyway, but this is my suggestion for now.

Regards,

Stefan

Aggr ( Aggr + Aggr)

matt_crowther — Fri, 24 Feb 2012 16:36:38 GMT

To generate your 'buckets' look to create a Calculated Dimension using the class() function - look in the Help file as it explains it better than I ever could.

This will put the results into seperate buckets as required negating the need to use seperate if() functions in the chart expressions. You can make the buckets dynmaic buy using either a calculation (to set them to be 20% each say) or via a variable to allow the user to adjust them.

Hope that's of use,

Matt - Visual Analytics Ltd

Qlikview Design Blog: http://QVDesign.wordpress.com

@QlikviewBI

Aggr ( Aggr + Aggr)

Fri, 24 Feb 2012 17:54:19 GMT

Can you elaborate on the pick function? That is new for me and I like to see it used practically...

Kr,

Dion

Re: Aggr ( Aggr + Aggr)

swuehl — Fri, 24 Feb 2012 18:15:08 GMT

I actually used the pick function in the background color expression, can you open the file?

Pick() is a conditional functions (like case in C), this is what the Help says:

pick(n, expr1[ , expr2,...exprN])

Returns the n:th expression in the list. n is an integer between 1 and N.

Example:

pick( N'A''B'4, , , )

returns 'B' if N = 2

returns 4 if N = 3

So, I used for the color attribute expression:

=pick(

aggr(

floor((

if((SUM(DataCOPQWarranty) + SUM(DataCOPQInternal))/ SUM(if(isnull(Spend),DataSpend,Spend)) < 0.0075,'1'

, if(

(SUM(DataCOPQWarranty) + SUM(DataCOPQInternal))/ SUM(if(isnull(Spend),DataSpend,Spend)) >= 0.0075 and

(SUM(DataCOPQWarranty) + SUM(DataCOPQInternal))/ SUM(if(isnull(Spend),DataSpend,Spend)) < 0.01, '2'

,if(

(SUM(DataCOPQWarranty) + SUM(DataCOPQInternal))/ SUM(if(isnull(Spend),DataSpend,Spend)) >= 0.01 and

(SUM(DataCOPQWarranty) + SUM(DataCOPQInternal))/ SUM(if(isnull(Spend),DataSpend,Spend)) < 0.015, '3'

,if(

(SUM(DataCOPQWarranty) + SUM(DataCOPQInternal))/ SUM(if(isnull(Spend),DataSpend,Spend)) >= 0.015, '4')

)))

+if((SUM(DataQOD) * 1000000) / SUM(DataQR) <= 300, '1'

,if(((SUM(DataQOD) * 1000000) / SUM(DataQR) > 300) and ((SUM(DataQOD) * 1000000) / SUM(DataQR) <= 2500), '2'

,if(((SUM(DataQOD) * 1000000) / SUM(DataQR) > 2500) and ((SUM(DataQOD) * 1000000) / SUM(DataQR) <= 4000), '3',

if(((SUM(DataQOD) * 1000000) / SUM(DataQR) > 4000), '4')

))))

/2)

,DataSupplierName),

rgb(173,255,47),rgb(255,255,0),rgb(255,165,0),rgb(255,0,0))

The lengthy aggr() function as first parameter is just replicating your dimenison value. This will return a value from 1 to 4 as Quality measure, this value will then "pick" the correct rgb() function.

I am attaching a corrected version also, I missed a bracket in the previous (A+B)/2 calculation for the Quality.

Have a nice weekend,

Stefan

edit:

Forgot to mention, one big advantage of using a calculated dimension instead of your four expressions is that you actually can select a Quality with all its underlying Supplier Names. That's cool!