topic Re: Using a string to get a measure name in App Development

Using a string to get a measure name

JuMo — Wed, 20 Nov 2024 13:26:28 GMT

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 ?

Re: Using a string to get a measure name

Qrishna — Wed, 20 Nov 2024 11:53:58 GMT

Hi - When you say 'Using the same approach,..' whats the approach, could you provide the expression/code for that so we cn repeat the same and check if that works for the second case too?

Re: Using a string to get a measure name

JuMo — Wed, 20 Nov 2024 13:25:36 GMT

Well the variable apporach was simple:

- There is a variable called KPI_Analyzed_Key

- There is a variable input drop down populated by my table/column KPI_Key and the value is saved in the variable KPI_Analyzed_Key

- To display a measure according to the selected variable, I use the following expression

[$(KPI_Analyzed_Key).Monthly]

And surprisingly, it works

Re: Using a string to get a measure name

Lech_Miszkiewicz — Wed, 20 Nov 2024 13:33:59 GMT

Hi @JuMo

variable always holds single value at the time. So if you expect it to expand to different values for each row of your table - it will not work. Instead you may want to consider using function like Only() or MaxString() to pickup the value from the dim.

Something like: [$(=Only(KPI _Key)).Monthly]

I would then preview how this expands in expression editor

cheers

Re: Using a string to get a measure name

JuMo — Wed, 20 Nov 2024 14:43:59 GMT

Thanks .... The dollar sign seams to give an incorrect result, he is my result

KPI_Key	Only(KPI_Key)	$(=Only(KPI_Key))	[$(=Only(KPI_Key)).Monthly]
KPI_A	KPI_A	-	-
KPI_B	KPI_B	-	-
KPI_C	KPI_C	-	-

Re: Using a string to get a measure name

lennart_mo — Wed, 20 Nov 2024 15:56:47 GMT

Hi @JuMo,

Try using the GetObjectField() Function, it should return the KPI-Key from each row, if you write it as a $-Sign expression.

So using something like [$(=GetObjectField(0)).Monthly] might work.

Re: Using a string to get a measure name

Qrishna — Wed, 20 Nov 2024 21:30:46 GMT

whats your expected output?

something like this:

Re: Using a string to get a measure name

JuMo — Thu, 21 Nov 2024 06:15:45 GMT

Thanks for your help, however, it's not working:

[$(=GetObjectField(0)).Monthly] returns "-"

KPI_Key	[$(=GetObjectField(0)).Monthly]
KPI_A	-
KPI_B	-
KPI_C;	-

$(=GetObjectField(0)) returns a string with the KPI key

KPI_Key	$(=GetObjectField(0))
KPI_A	KPI_A
KPI_B	KPI_B
KPI_C;	KPI_C

GetObjectField(0) returns the name of the dimention

KPI_Key	GetObjectField(0)
KPI_A	KPI_Key
KPI_B	KPI_Key
KPI_C;	KPI_Key

Re: Using a string to get a measure name

JuMo — Thu, 21 Nov 2024 06:16:25 GMT

Yes, exactly

Re: Using a string to get a measure name

Lech_Miszkiewicz — Thu, 21 Nov 2024 07:13:34 GMT

Hi @JuMo

Can you create simple Ctrl + 00 app ( just create new app and hit CTRL + 00 on keyboard in load script). That will autogenerate script for testing. Use this data to replicate sample you are trying to do then post it here.

With actual app will be much quicker than going back and forth.

cheers

Re: Using a string to get a measure name

Muthukumar_77 — Thu, 21 Nov 2024 10:42:38 GMT

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,

Re: Using a string to get a measure name

Qrishna — Thu, 21 Nov 2024 12:34:41 GMT

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)

Re: Using a string to get a measure name

JuMo — Thu, 21 Nov 2024 13:49:25 GMT

Thanks for your help ...

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

Re: Using a string to get a measure name

JuMo — Thu, 21 Nov 2024 13:50:39 GMT

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

Re: Using a string to get a measure name

Qrishna — Thu, 21 Nov 2024 14:34:51 GMT

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?

Re: Using a string to get a measure name

JuMo — Thu, 21 Nov 2024 14:34:03 GMT

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

Re: Using a string to get a measure name

marcus_sommer — Thu, 21 Nov 2024 15:59:21 GMT

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.

Re: Using a string to get a measure name

JuMo — Thu, 21 Nov 2024 16:31:21 GMT

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

Re: Using a string to get a measure name

JuMo — Thu, 21 Nov 2024 16:56:15 GMT

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]

Re: Using a string to get a measure name

marcus_sommer — Thu, 21 Nov 2024 17:02:51 GMT

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.