topic Grouping Values on list box in QlikView

Grouping Values on list box

Mon, 16 Feb 2015 11:49:40 GMT

Hi all,

I'm new to using QV and this might be a stupidly simple question but would really like your help here.

I am creating a dashboard to show difference between budget and actual spending for each entity within a business (there are about 50). I created a list box showing this difference, but instead of displaying individual difference (e.g. -93322.00, -84466.00, -83492.00,-27418.00,0,123.00 ...and so on ), I want to show by intervals such as -100,000--50,000, -50,000-0, 0-50,000 etc.

Is this possible?

Thanks for your help in advance.:)

Michelle

Re: Grouping Values on list box

marcus_sommer — Mon, 16 Feb 2015 16:53:39 GMT

You could cluster such calculation with class() or use a rounding-function, maybe round(value, 10000).

- Marcus

Re: Grouping Values on list box

Mon, 16 Feb 2015 16:57:05 GMT

Hi Michelle,

It is possible using intervalmatch() function at script level. Then you can show intervals in listbox as desired. Hope this helps.

Regards

Re: Grouping Values on list box

Tue, 17 Feb 2015 10:44:11 GMT

Thanks Marcus, I think the class( ) function is closer to what I'm trying to achieve. With the above example though I have entered the expression as Class (Difference, 25000); on the list box it would still show the actual value then the class e.g. -93322.00 -94000.00<=00.00<-69,000, -84466.00 -94000.00<=00.00<-69,000

Do you know how I could get rid of the actual value and only show the each class once?

Thank a lot again

Re: Grouping Values on list box

marcus_sommer — Tue, 17 Feb 2015 10:59:39 GMT

If you want to use such expression-clustering directly within the gui in a listbox or a chart you will need a wrapping from these expression with aggr(), maybe in this way:

= Class(aggr(EXPRESSION, DIMENSION), CLUSTER-SIZE, START

or more practical

= Class(aggr(sum(KPI) / Count(DISTINCT Month&Year), Store), $(Cluster), 'x', 0)

whereby $(Cluster) is a variable to increase the flixibility.

- Marcus