Using a field's value as reference to another field

[#1]

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

[tresesco]

possibly this :

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

not tried.

Regards, tresesco

[Miguel_Angel_Baeyens]

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.

[#4]

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?

[#5]

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

[Miguel_Angel_Baeyens]

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.

[#7]

Hi Miguel,

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

cheers

Florian

[Miguel_Angel_Baeyens]

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.

[#9]

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

[#10 · Accepted solution]

@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