Using a string to get a measure name

[JuMo]

Hello

I have a series of measure that have the following format

KPI_A.Monthly

KPI_A.Target

KPI_A.Accumulative

KPI_A.EOY

KPI_B.Monthly

KPI_B.Target

KPI_B.Accumulative

KPI_B.EOY

KPI_C.Monthly

… etc …

I also have a table with the root name of the measures

KPI_Key

KPI_A

KPI_B

KPI_C

…

1) 1st Method

If I inject this table inside a variable (KPI_Analyzed_Key), I can (with a drop-down selector) select a KPI_Analyzed_Key and then display all the associated measure using this expression:

[$(KPI_Analyzed_Key).Monthly]

And it works perfectly, I’ve got the monthly value for the selected KPI

2) 2nd Method

Using the same approach, I want to display all my measure in the same table… I would like to have something like that

KPI_Key	Monthly	Target	Accumulative	EOY
KPI_A
KPI_B
KPI_C

However, I cannot manage to get it …

I’ve tried to duplicate the same formula as previously:

[$(KPI _Key).Monthly]

but its not working.

Any clue ?

[Muthukumar_77]

Hi,

Try below scrpt,

Table:

Load * Inline [

KPI_KEY_RET

KPI_A

KPI_B

KPI_C

];

Table_1:

Load * Inline [

Attributes,Value

KPI_A.Monthly,200

KPI_A.Target,300

KPI_A.Accumulative,400

KPI_A.EOY,500

KPI_B.Monthly,100

KPI_B.Target,400

KPI_B.Accumulative,600

KPI_B.EOY,400

KPI_C.Monthly,800

KPI_C.Target,200

KPI_C.Accumulative,300

KPI_C.EOY,900

];

NoConcatenate

Table_2:

Load * Where Exists(KPI_KEY_RET,KPI_KEY); //To fetch only list of "KPI KEY" from "Table"

Load SubField(Attributes,'.',1) as KPI_KEY,

SubField(Attributes,'.',2) as Measures,

    Value as MeasuresValue

Resident Table_1;

Drop Table Table_1,Table;

FinalTable:

Load Distinct KPI_KEY Resident Table_2;

FOR i = 1 to FieldValueCount('Measures')

    LET vField = FieldValue('Measures', $(i));

    Left Join (FinalTable)

    LOAD KPI_KEY,

        MeasuresValue as [$(vField)]

    Resident Table_2 Where Measures = '$(vField)';

NEXT i

DROP Table Table_2;

Output,

[Qrishna]

I highly suggest doing this in script rather than storing the values in the variables, as pivot tables isknow for its notorious nature especially for rangesum and variable usage expression. to avoid those, you could try below.:

Measures:
Load *,
text(subfield(Measure, '.', 1)) as Measure_Root,
text(subfield(Measure, '.', 2)) as Measure_attribute,
text(subfield(Measure, '.', 1)) as Measure_Root_Key
;
LOAD * Inline [
Measure
KPI_A.Monthly
KPI_A.Target
KPI_A.Accumulative
KPI_A.EOY
KPI_B.Monthly
KPI_B.Target
KPI_B.Accumulative
KPI_B.EOY
KPI_C.Monthly
KPI_C.Target
KPI_C.Accumulative
KPI_C.EOY
] ;

table_Root_Name:
load text(KPI_Key) as Measure_Root_Key,
KPI_Key
inline [
KPI_Key
KPI_A
KPI_B
KPI_C
];

UI:

Pivot table:
Dim1: KPI_Key
Dim2: Measure_attribute
Expression: =only(KPI_Key)&'.'&only(Measure_attribute)

[JuMo]

Thanks for your help ...

Hereunder the result ... Unfortunately, the cells displays the name of the KPIs, not the result 

[JuMo]

I've reached an admin of my platform, because I dont have the rights to generate a QVS

[Qrishna]

In response to Qrishna

Yes, exactly

so i provided that code.

so what do you want? could you put output view in an excel and paste it so i can understand what output view excactly your looking for?

[Qrishna]

Yes, exactly

[JuMo]

Thank you, nevertheless I your example, the "Measures" are included in the script, and that not what I need, my measure a more complicate, they are aggregations of several fields

[marcus_sommer]

What you want to get isn't possible - you could not include dimension-information as expression-field-parts because they won't accepted as fields/functions/... else being just strings - unless they would be wrapped by $-sign expansion. But then they are an adhoc-variable which will be evaluated before the object is calculated and this single value is then applied to all rows/columns.

Therefore a simple dynamic expression in which in each cell a different calculation is performed is not possible. If you want this kind of logic you will need a (nested) if-loop which queries the dimension-values and branched then to the wanted calculation. Be aware that such method would by larger data-sets slow down the application significantly.

[JuMo]

Of course, those are my KPIs

Ad I want all them in this kind of table:

	Monthly	Target	EOY
KPI_A	1990	250800	864
KPI_B	2990	253200	8,91
KPI_C	2080	303400	5040

[JuMo]

I am quite  surprised because I can do that using variable:

 

Those are the expression used for each cards:

[$(KPI_Analyzed_Key).Monthly]

[$(KPI_Analyzed_Key).Target]

[$(KPI_Analyzed_Key).EOY]

[marcus_sommer]

It's a single expression in each card and of course they could be combined with variables. But not using variables else trying to reference on dimension-values to build an expression is a different matter.