Dynamic Measures

[edwin]

I have a requirement that i think requires me to save the name of a measure in a field and in a straight table, retrieve the name of the measure, use the retrieved value in an expression.  i do have an alternative solution that addresses this requirement on the data model side but im looking for a solution on the frontend side. 

to uncomplicate the problem , i have 2 dimensions and 3 measures:
the first 2 sets of dim values have entries for Measures1 and 2, for these dimensions, Measure3 is irrelevant.
for the 3rd and 4th dims, Measure1 is irrelevant:

i created 2 new fields that stores what measures to display.  
so for the 1st 2 dims, i want to display Measures 1 and 2, for the next set, Measures 2 and 3
my configuration table is shown below:

at the end of everything, i want the following visual:

for now please dont worry that this will not make sense to the user.  this visual is simplified not to muddle the problem.

you would think that the expression =sum([$(=Only(DisplayMeasure1))]) should suffice.  the value for DisplayMeasure1 for dim A will evaluate to Measure1, substitute that, evaluate the sum and done.  but no - it appears the phrase ONLY(DisplayMeasure1) is evaluated even before the dimensions are looked at so if no dimensions are selected, the column is blank bec there are multiple values of DisplayMeasure1.  to illustrate this:

No selections:

select Dim1=A:

bec if Dim1=A, only(DisplayMeasure1) is evaluated as Measure1.  Ive already tried using aggr by Dim1, Dim2.  didnt work

you would say why not use an if statement?  yes that will work for this simple example, but my actual requirement has 10 different sets for dimensions with over 50 measures.  so that wont work.

as i have said at the start, we solved this using a data model change but what i am looking for is a front end solution.  im looking for that sweet brilliant dynamic expression.  thanks to anyone who loves a good challenge and gives their time to this.

[Or]

Actually, I think your issue here is something a tad different from what you think - you're trying to nest aggregation functions by putting an ONLY() inside a SUM(). Qlik doesn't allow this, so you'll have to either aggr() to get around the nested aggregation, or not use the inner Only() (unsure if it's necessary for this to work, I think it wouldn't be but I'm not sure).

[edwin]

@Or  thanks for looking into this.  actually it does work - if you look at the attached QVW and:

only(DisplayMeasure) returns "Measure1" and then evaluates the sum.

[edwin]

so for applications where the measure is dedicated to that particular column (off the cuff i could think of a self serve type table) this expression will work fine

[edwin]

to phrase the problem simply: 
if i have a field that contains the name of another field which is a measure.   how do i phrase the expression:
[MeasureDisplay] field has its value as "Measure1" where Measure1 is the name of a valid field. 
how do i code sum(Measure1) using MeasureDisplay field so that it is dynamic.

[Or]

You can calculate Only(Field) once in the variable and then calculate Sum of the result, yes. But you can't, on the fly, calculate Sum(Only(Field)) within the context of the table row. That would be a nested aggregation and if you try typing it into the formula editor, you'll get an error. Because your requirement appears to be evaluating within each row (you want it to consider the dimensions), that can't work, I think, even if you were able to get Qlik to evaluate the formula for each row rather than once. Possibly I am misunderstanding the order in which things are being applied, though - perhaps someone else will have a better understanding.

If this isn't too much of a simplification of the original, you could presumably just use if() statements to pick out the correct formula based on the variable?

[edwin]

im actually not using sum(Only(Field)) - i agree that will not work. 

im using =sum([$(=only(Field))]).  theres a diff

[edwin]

besides the QVW already proves it works in a limited sense - just not the one i require.

[eddie_wagt]

Hello @edwin ,

Is this something you seek? The problem you encounter lies within the fact you want to evaluate two measures in one expression box. Therefore you should do something with the functions pick, match. To make some things easier for you, there is a function in Qlikview that can be handy : GetObjectField. 

For making things work I have made a variable: getObject . 

Anyway, your project can be further enhanced and spiced up, but till then you should have a look if this meets your requirements.

Regards Eddie

[edwin]

@eddie_wagt thanks for the response and for your time.  as i said we have about 50 measures and using a pick or if will not be sustainable for us.

our backend solution is to consolidate all the measures in new columns .  to illustrate, if we want Measure1 for Dim1 under column1 and Measure2 for Dim2 under same column, we created a new MeasureDisplay1 and save the values of the specific columns per dim.  we have 10 different scenarios with over 50 measures across all dimensions.  however, since some dims dont have some of the measures, the resulting columns result to only about 16.  so we created 16 DisplayMeasures and slot the appropriate measures.  of course we also stored the name of the measures in a mapping table to show the user what measure they are viewing for the specific dimension.

this is all on the DM side, so the expression is just sum(DisplayMeasure1) for the first column.  avoiding the use of if or pick functions.

still, i was hoping for a front end solution.