topic Re: Dynamic Measures in QlikView

Dynamic Measures

edwin — Tue, 23 Mar 2021 14:46:55 GMT

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.

Re: Dynamic Measures

Or — Tue, 23 Mar 2021 13:42:25 GMT

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).

Re: Dynamic Measures

edwin — Tue, 23 Mar 2021 14:37:54 GMT

@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.

Re: Dynamic Measures

edwin — Tue, 23 Mar 2021 14:39:19 GMT

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

Re: Dynamic Measures

edwin — Tue, 23 Mar 2021 14:43:00 GMT

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.

Re: Dynamic Measures

Or — Tue, 23 Mar 2021 16:41:52 GMT

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?

Re: Dynamic Measures

edwin — Tue, 23 Mar 2021 16:34:40 GMT

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

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

Re: Dynamic Measures

edwin — Tue, 23 Mar 2021 16:35:19 GMT

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

Re: Dynamic Measures

eddie_wagt — Thu, 25 Mar 2021 13:14:08 GMT

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

Re: Dynamic Measures

edwin — Thu, 25 Mar 2021 16:48:43 GMT

@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.

Re: Dynamic Measures

edwin — Thu, 25 Mar 2021 17:10:30 GMT

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:

Re: Dynamic Measures

eddie_wagt — Fri, 26 Mar 2021 08:25:08 GMT

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.

Re: Dynamic Measures

edwin — Fri, 26 Mar 2021 14:36:01 GMT

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

Re: Dynamic Measures

eddie_wagt — Mon, 29 Mar 2021 07:09:44 GMT

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