Sum total in set analysis

[w21450]

Hi all

I am having problems with a tricky set analysis.

Just a quick summary - i'll try to be brief.

So I have a table in Qlik, as you can see in the attached photo 1. In this table I have a range of different values in the first column "ejendoms_id". The values can be repeated depending on if they have multiple subtasks in the "opgave_id" column. I have then made two "varible input" boxed so it can be filtered depending on input. The variable input boxes relate to the last column "Justering model". So the value in "Justering model" has to be between the two inputs in order to be shown. However, right now it goes through line by line and evaluates whether it is within range - which of course makes sense.

What I really want it to do, is evaluate depending on the dimension in the first column "ejendoms_id". So I want it to show the columns ONLY if the SUM of "Justering model" for each "ejendoms_id" is within the input range. For example i want it to check if the sum of "Justering model" for ALL "ejendoms_id" = 1 is within the input range.

How do I do this in the set analysis? The equation I have right now is:

Sum({<[justering_model]={">=$(vJusteringModelMin)"}>*<[justering_model]={"<=$(vJusteringModelMax)"}>} justering_model)

I want to make the two constraints that depend on the variable input sum across the specific dimension "ejendoms_id".

I hope it makes sense, and that someone can help.

Best, 

Peter

[rubenmarin]

Hi, you can you try this?: Sum({<[ejendoms_id]={"=Sum([justering_model])>=$(vJusteringModelMin) and Sum([justering_model])<=$(vJusteringModelMax)"}>} [justering_model])

[w21450]

It does not seem to work when I try to use it. What i the logic behind it - could you explain?:)

I Have just tried to use an aggr function on the constraints inside the set expression as this would seem like the most logic thing to do. But it just won't output any results in the table. I have used the following expression with aggr:

=sum({<aggr(sum([justering_model]),ejendoms_id)={">=$(vJusteringModelMin)"}>*<aggr(sum([justering_model]),ejendoms_id)={"<=$(vJusteringModelMax)"}>} justering_model)

Am I perhaps making some syntax errors with the inner aggr function?

[w21450]

It does not seem to work when I try to use it. What i the logic behind it - could you explain?:)

I Have just tried to use an aggr function on the constraints inside the set expression as this would seem like the most logic thing to do. But it just won't output any results in the table. I have used the following expression with aggr:

=sum({<aggr(sum([justering_model]),ejendoms_id)={">=$(vJusteringModelMin)"}>*<aggr(sum([justering_model]),ejendoms_id)={"<=$(vJusteringModelMax)"}>} justering_model)

Am I perhaps making some syntax errors with the inner aggr function?