
Re: Aggr ( Aggr + Aggr)
Stefan Wühl Feb 24, 2012 11:36 AM (in response to dionverbeke)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

SupplierScorecard2_SW.qvw 375.5 K

Aggr ( Aggr + Aggr)
dionverbeke Feb 24, 2012 12:54 PM (in response to Stefan Wühl )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)
Stefan Wühl Feb 24, 2012 1:16 PM (in response to dionverbeke)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!

SupplierScorecard2_SW2.qvw 375.8 K




Aggr ( Aggr + Aggr)
Matthew Crowther Feb 24, 2012 11:36 AM (in response to dionverbeke)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