topic suming variables in expressions in QlikView

suming variables in expressions

Mon, 30 Apr 2012 12:26:45 GMT

Hi guys,

I saw in a qlikview example "whats new in qlikview 11" that you could create "slect one metric" for sales, quantity, margin...etc..

The way they did it:

MetricsOptions:

LOAD * INLINE [_metricsNo, _metrics

1, Sales,

2, Qty,

3, Margin

4, Margin%

];

MetricPickOne:

LOAD DISTINCT

_metrics as _metricPickOne

RESIDENT MetricsOptions;

My question is, if I wanted to sum up sales in a expression but don´t want to hardcode sum(sales) but instead wanted to use the Metrics Options, how do I do that? I tried sum(_metricPickOne) but that didn´t work.

I know I can use condition and say _metricPickOne = 'sales' than sum(sales) but what if I have more than 10 metrics? Is there another way?

Regards,

alphez

Re: summing variables in expressions

Mon, 30 Apr 2012 16:50:10 GMT

If you Select one value from either your _metricsNo or _metrics fields then this expression would work:

sum($(=Only(_metrics)))

or sum($(=_metrics))

Re: summing variables in expressions

Anonymous — Mon, 30 Apr 2012 18:31:37 GMT

The safest way to code this would be to use minstring() instead of only(). This way, no selection will still work and it will also protect against losing the one and only one selection (which I would recommend) on _metrics.
sum($(=minstring(_metrics)))

suming variables in expressions

Mon, 30 Apr 2012 18:34:44 GMT

If I create a text box in the example and try your solution, i get the null sign '-'.

suming variables in expressions

Wed, 02 May 2012 09:58:40 GMT

sum($(=minstring(_metrics))) will work but the problem is you well always get the same number no matter whether you choose sales, Qty, Margin or Margin%. Thats because minstring(_metrics) always returns Margin

suming variables in expressions

jagan — Wed, 02 May 2012 11:52:42 GMT

Hi,

MinString() returns minumum value in the list,

If you select a value then it returns selected value.

If you select multiple values then it minimum value in the selected values.

If you do not select any values then it returns minimum values of the overall list.

Hope this helps you.

Regards,

Jagan.