topic Re: Creating Range calcuation in dimensions in QlikView

Creating Range calcuation in dimensions

qlik_venu — Tue, 02 Aug 2016 07:05:51 GMT

Hi,

I have a requirement to create Ranges based on measures and then use them as dimension to display other metrics.

Here is the table 1 I have ( Added Below), the last column Calculated Column - Item Range I get from the previous column Sales and has to be calculated in Qlikview.

Table 1

			Calculated Column
	Sales	Packs within Item	Item Range
Item 1	100	20	Item Range 1 ( 0 to 200 Sales)
Item 2	200	25	Item Range 1 ( 0 to 200 Sales)
Item 3	300	30	Item Range 2 ( 200 to 400 Sales)
Item 4	400	10	Item Range 2 ( 200 to 400 Sales)
Item 5	350	23	Item Range 2 ( 200 to 400 Sales)
Item 6	600	32	Item Range 3 ( 400 to 600 Sales)

The Requirement is to create a Bar chart with the below measures. Can you please suggest me how to achieve this.

Currently I am creating this range in the Load script and using that as a dimension in my chart. But this would remove any dynamic filters which can be used on this dimension. So I am looking for a better approach. Thanks !!

Chart

Chart
Item Range	Count of packs in the Range	Sum of Sales
Item Range 1 ( 0 to 200 Sales)	45	300
Item Range 2 ( 200 to 400 Sales)	63	1050
Item Range 3 ( 400 to 600 Sales)	32	600

Re: Creating Range calcuation in dimensions

Tue, 02 Aug 2016 07:14:02 GMT

how you are creaing Item range in script? if answer is using interval match() function, then you have done it correctly.
what do you mean by "Dynamic filters"?

Re: Creating Range calcuation in dimensions

phaneendra_kunc — Tue, 02 Aug 2016 07:20:15 GMT

try using CLASS function.

class - script and chart function ‒ Qlik Sense

Re: Creating Range calcuation in dimensions

jonathandienst — Tue, 02 Aug 2016 07:25:08 GMT

>>But this would remove any dynamic filters which can be used on this dimension.

Creating a range field in script should not affect your ability to filter on this or other dimensions unless you are aggregating the records in the load. But perhaps I am not understanding your problem correctly. Do you mean you want to apply the ranges dynamically?

Re: Creating Range calcuation in dimensions

maxgro — Tue, 02 Aug 2016 07:29:56 GMT

maybe this, .qvw in the attachment

Re: Creating Range calcuation in dimensions

qlik_venu — Tue, 02 Aug 2016 07:46:00 GMT

I am just using a normal if condition in the script.

I was referring to the normal selections being made in qlikview as the dynamic filters. Once I define my range based on the Sum(Sales), I will not be able to apply a filter on the sales measure used to define the range. For eg. I cant define a Range based on Sals only for US region. Since the group is already defined, I can only show the range at the global level.

Re: Creating Range calcuation in dimensions

qlik_venu — Tue, 02 Aug 2016 07:47:37 GMT

Yes I am aggregating the measure in the Load. Sum(Sales) is an aggregated expression.

Re: Creating Range calcuation in dimensions

qlik_venu — Tue, 02 Aug 2016 07:47:44 GMT

I will try this.

Re: Creating Range calcuation in dimensions

qlik_venu — Tue, 02 Aug 2016 07:50:00 GMT

I will check this approach.

Re: Creating Range calcuation in dimensions

qlik_venu — Tue, 02 Aug 2016 17:36:59 GMT

Hi,

The Class function works fine, but rom my clients requirement it appears that the class intervals are not the same. Just saw this.

Any suggestions,

Venu