topic Using a field's value as reference to another field in QlikView

Using a field's value as reference to another field

Tue, 08 Mar 2011 10:25:57 GMT

Hi Community,

I have an independent table that defines KPIs like Qty, Revenue or whatever. I am looking for a way to dynamically calculate the KPIs in a Pivot/Table. I use the KPI values (not connected to the data model itself!) as dimension. Now I want a simple dynamic expression which makes a sum(Qty) for the Qty line, a sum(Revenue) for the Revenue line and so on. Sample data structure is below and any hint or solution is highly welcome.

KPIs:

LOAD * INLINE[

KPI

Qty

Revenue

]

Data:

LOAD * INLINE [

Country

Product

Qty

Revenue

]

cheers

Florian

Using a field's value as reference to another field

tresB — Tue, 08 Mar 2011 10:46:31 GMT

possibly this :

define a variable Vexpression=KPI and then the expression like Sum($(Vexpression)).

not tried.

Regards, tresesco

Using a field's value as reference to another field

Miguel_Angel_Baeyens — Tue, 08 Mar 2011 10:46:53 GMT

Hello Florian,

Asusming the folllowing piece of script based on yours

KPIs:LOAD * INLINE [KPI, ExpressionQty, Sum(Qty)Revenue, Sum(Revenue)]; Data:LOAD Repeat(Chr(64 + Ceil(Rand() * 15)), 2) AS Country, 'Item' & Repeat(Chr(64 + Ceil(Rand() * 15)), 3) AS Product, Ceil(Rand() * 1000) AS Qty, Ceil(Rand() * 100) AS RevenueAUTOGENERATE 20;

Now create a Listbox with field KPI, select one value and in the Listbox properties, check "Alway one value selected" and a new Pivot Chart, set Product as dimension (or Country) and the following as expression

$(=Only(KPI))

You can set a condition calculation so if there's no KPI value selected, the chart is not rendered.

Hope that helps.

Using a field's value as reference to another field

Tue, 08 Mar 2011 10:50:27 GMT

Hi,

You just have to create a variable called for example v_kpi which value is =only(KPI), then in your chart you can use as expression the value sum($(v_kpi)) . When you select "Revenue" as your kpi the value of the variable will be sum(Revenue) and the chart will show these values, if you select qty the chart will be updated with the sum of the quantities. Is that what you were asking for?

AW:Re: Using a field's value as reference to another field

Tue, 08 Mar 2011 11:11:08 GMT

Hi All,

you all have basically the same approach, but the returned value remains 0 or NULL depending on the used syntax. Furthermore my challenge is, that these KPIs are not exchangable, but need to be shown all at the same time. I just thought it would be easer to work with such a reference instead of having multiple objects and calculations to maintain...

cheers

Florian

AW:Re: Using a field's value as reference to another field

Miguel_Angel_Baeyens — Tue, 08 Mar 2011 11:15:55 GMT

Hello,

Attached is a working copy of this approach.

If they need to be shown all at the same time, and they do not depend on the user selections, then why you don just use the corresponding expressions in the chart?

Regards.

AW:Re: AW:Re: Using a field's value as reference to another field

Tue, 08 Mar 2011 11:21:51 GMT

Hi Miguel,

your example works because you used product as dimension, but I need to have the different KPIs as dimension instead.

cheers

Florian

AW:Re: AW:Re: Using a field's value as reference to another field

Miguel_Angel_Baeyens — Tue, 08 Mar 2011 11:35:25 GMT

Hi Florian,

So if I understand you right, then you will need to use the Aggr() function to get those expressions as dimensions. You can add that Aggr() to the KPI INLINE table, for example

Qty, Aggr(Sum(Qty), Product)

Apologies if this is not what you want.

AW:Re: AW:Re: Using a field's value as reference to another field

Tue, 08 Mar 2011 11:42:40 GMT

Hi, not sure to understand what you want, but it sounds like cross table, is that something like that that you are expecting ?

AW:Re: AW:Re: AW:Re: Using a field's value as reference to another field

Tue, 08 Mar 2011 11:57:43 GMT

@Miguel,

didn't work out in my particular example as basically the dimension to aggregate over would change in my use case. However, thank you very much for your efforts.

@Renaud,

not exactly, as I won't use product and country in my visualization at all.

@All,

I solved it through some nested IF statements. It doesn't look nice and takes some performance, but luckily my application is small enough to handle it.

cheers

Florian