topic Re: Ratio on value from two selected dimensions in QlikView

Ratio on value from two selected dimensions

Anonymous — Fri, 08 Jun 2012 13:31:58 GMT

Hi,

I have a list of items together with a price and what i want to do is choose two items and get the ratio between these to items price. And i want to present this in a chart where the dimension is the two items together, hence "Item 1 / Item 2"

Is this possible?

Re: Ratio on value from two selected dimensions

vincent_ardiet — Fri, 08 Jun 2012 14:38:53 GMT

Hi,

You can use alternate states to do this.

Define a new alternate state (settings/document properties/general : alternate states button) and create two listboxes with the same dimension.

Associate one of those listboxes to the alternate state you have defined (list box properties/general).

Then in your chart, you can use set analysis to compare the two selections for example like :

=sum(amount) / sum({$<country=p({nameOfAlternateStateJustCreated} country)>} amount)

I use {$<country=p({nameOfAlternateStateJustCreated} country)>} in order to just redefine the dimension country and to keep all others dimensions.

If you want to know more, don't hesitate to have a look to the "whats new in Qlikview11" application in the sheet "Sales Overlap".

Regards,

Vincent

Ratio on value from two selected dimensions

Anonymous — Mon, 11 Jun 2012 07:03:53 GMT

Thanks alot!

But how do i write if i want to show the outcome in a straight table? e.g. like this, dimension is both states;

Default Dimension&' - '&Alternate Dimension

But i don't know how to get the Alternate Dimension in the dimension.

Any idea?

Thanks again!

//A.

Re: Ratio on value from two selected dimensions

vincent_ardiet — Mon, 11 Jun 2012 07:39:16 GMT

You can use "concat" like this in the label on your expression or of your dimension :

concat(distinct country,';') &' - '& concat({$<country=p({nameOfAlternateStateJustCreated} country)>} distinct country)

Regards,

Vincent

Ratio on value from two selected dimensions

Anonymous — Mon, 11 Jun 2012 07:52:11 GMT

Thanks, but the problem is that i want the dimension to be the result of this expression, not the lable of the dimesion. e.g. if i have severlat alternate states i want to list them like this;

Dimension Ratio year 1 (expression)

State1 & State 2 0.99

State 2 & State 3 0.86

Is it possible?

//A.

Re: Ratio on value from two selected dimensions

vincent_ardiet — Mon, 11 Jun 2012 09:02:08 GMT

For my point of view you can't do this.

You have to create one expression per ratio (state1&state2, state2&state3...).

Or you can use ValueList to create a manual dimension and then test the result in your expression but it seems to be complicated for the same result.

Regards,

Vincent