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.

[edwin]

here is a snapshot (not a lot of it) of a prototype we created using the DM solution.  this is just a single straight table.  the measures are different depending on the dimensions, some have more measures than others and they are blank where the column is not relevant:

[eddie_wagt]

Hello @edwin ,

Just to be sure, I saw the screenshot. I have seen your requirement to solve it front-end. If you want more flexibility and to solve this permanently you have to do some backend work to make this happen. The table I provided you is something fancy that could might work if you put more effort in it. I have solved some of the barriers you dealt with. The problem you face is still not out of the box solvable, because you want to evaluate multiple measures in one column/expression box. 

There might be a slightly opportunity that you can enhance / develop my solution a bit further. I might even help you, but believe me...the best you can do is solve this in the backend. See example.  This is one solution (star model) and you can enhance it by adding expressions to evaluate if necessary. But first step is to model your data differently.

If you definitely want it to solve it front-end than you have to expand the formula in a way that you count per dimension the number of values to determine a generic expression which can be fold out after the pick/match. 

[edwin]

i am aware that this can be solved by pick even using IF statements but i was looking for a more sustainable solution. 

we will stick to the DM solution for now until we find a simpler front end solution. 

the screenshot is an actual working QV straight table which we also implemented in Qlik Sense.  where the driving expression is a simple sum(displayMeasure1) - of course we used an if statement to format the header and totals but the main expression is a simple one.

thanks for your time

[eddie_wagt]

Hello @edwin ,

I am somewhat surprised that you have implemented the solution in Qlik Sense, because the syntax should be the same. As I already mentioned the driving expression does not work. I tested your model in Qlik Sense and you have the same problem in Sense as in Qlikview (the expression works only when you select a dimension). It doesn't matter, but I suspect your datamodel is a bit different than you provided.

If it is working in Sense than it should be working in View. 

Regards Eddie