topic Variable text boxes displaying metrics in QlikView

Variable text boxes displaying metrics

Mon, 13 Feb 2017 15:19:57 GMT

Hi,

I'm trying to make 10 text boxes that will display the value for any one of ~30 metrics for the first 10 entities selected in a list box.

My data contains Region, Area, Store, Sales, (and 30 other metrics). If a user selects 10 Stores, I would like the text boxes to display the name of the ten stores selected as well as the values for the selected metric.

For a simple example, if two stores are selected (Store1 and Store2), and the selected metric is Sales, then I would like the first two text boxes to show the sales for Store1 and Store2 respectively. I am using variables to get the store names that are selected:

vEntity1 = subfield(GetFieldSelections(Store_Name,',',20),',',1)

vEntity2 = subfield(GetFieldSelections(Store_Name,',',20),',',2)

etc.

I've managed to get the text boxes to show the names, but now I am struggling to find an easily scalable way to get them to also display the values for any single metric that is selected (in a list box). To display the Sales I currently have a separate Metric Definition for each text box stored in a table.

Metric_Name	Metric_Definition1	Metric_Definition2	...
Sales	sum({<Store_Name={$(=$(vEntity1))}>}[Sales])	sum({<Store_Name={$(=$(vEntity2))}>}[Sales])	...
Other metrics...	...	...	...

The first two text boxes then have the formula '=$(=Metric_Definition1)' and '=$=(Metric_Definition2)'.

When Sales is selected then the text boxes show the sales for the two selected Stores. If another metric is chosen, or other stores are selected then the values update.

This works, but since with this method I need to make 10 definitions for each metric (30+ metrics total), and I'd like to be able to select different Entity granularities (Region or Area as well), this will result in over 300 relatively complex metric definitions - lots of scope for things to break.

I'm wondering if anyone might have a better more streamlined way of doing this.

Essentially:

Select some stores
Select a metric
Have text boxes displaying the store name and metric value

Many thanks for any help - you will be saving me from a lot of headaches!

Re: Variable text boxes displaying metrics

vishsaggi — Mon, 13 Feb 2017 15:39:07 GMT

Why dont you use a straight table here? Can you share some sample data app and expected output to suggest quickly?

Preparing examples for Upload - Reduction and Data Scrambling

Re: Variable text boxes displaying metrics

Tue, 14 Feb 2017 10:17:40 GMT

Hi Vishwarath,

I've created a sample file trying to explain what i'd like to make. In the file the two text boxes show the first two of whichever stores are selected and the values for whichever metric is selected.

I understand that it displays the same information the Straight table does but this is more of an aesthetic thing.

My current method works, but i'd like an easier way to scale this up for more Stores and many more metrics. Currently there is a different version of each metric_definition for each textbox.

Many thanks,

Jeremy

Re: Variable text boxes displaying metrics

marcus_sommer — Tue, 14 Feb 2017 15:40:59 GMT

I think there is no real way to scale this approach - unless you would use macros which aren't recommended within the front-end and they would complicate the already complex approach a lot more.

What's wrong with a table - and you could use a lot of layout-stuff like borders, colors, multi-line and so on, too - ok. not quite so flexible as with textboxes but many things are possible (and if you used two or several tables which are laying over eachother you might overcome some restrictions like different fonts within a single cell).

- Marcus