Summing up the latest week

[QlikSensor]

Hi,

I have data per week. In a diagram I need to sum up values only of the latest week which is contained in the data. So I need something like this:

Sum({$<Week={max([Week])}> }[my Value])

But I  can't compile this formula....

[JordyWegman]

Hi,

Try this:

Sum({$<Week={"=max([Week])"}> }[my Value])

Jordy

Climber 

[QlikSensor]

Hi Jordy,

thanks for that. The formula is now compilable but the results seems to sum up anything else. If I write this

=max([Week])

into the title of the diagram, it is computed to 46. I copied the diagram and stated in one diagram:

Sum({$<Week={"=max([Week])"}> }[my Value])

and in the other:

Sum({$<Week={46}> }[my Value])

 and get this (see screenshot).

Any hints, why the result is different?

[JordyWegman]

Is week 46 really the max week in your data set? I seems like it's more. Try these kinds of formula's in tables first, this makes it easier to check and you can then also add dimensions it in.

Jordy

Climber

[tresesco · Accepted solution]

Your expression needs to be corrected a bit; Try like:

Sum({$<Week={'$(=max([Week]))'}> }[my Value]) 

[JordyWegman]

Hi Tresesco,

What is the exact difference in this one?

Sum({$<Week={'$(=max([Week]))'}> }[my Value]) 
                  vs
Sum({$<Week={"=max([Week])"}> }[my Value]) 

Does the $() execute the Max() function? The single or double quotes don't really matter in this one right?

Jordy

Climber

[tresesco]

Hi Jordy,

The basic difference would be there in the scope of evaluation of max([Week]) expression.

With the below expression:

Sum({$<Week={'$(=max([Week]))'}> }[my Value]) 

max() gets calculated against overall Week dimension, because of the fact that $-expansion happens first. So the expression gets executed against the max week value, i.e. -46 here. However,

with:

Sum({$<Week={"=max([Week])"}> }[my Value]) 

 it's a search string (because it starts with '=' sign and within double quotes). Here the scope of evaluation is per-dimension value. I believe you must remember the fact of advanced-search (using expression) nature of qlik here. Hence, effectively the expression calculates max value against each week value which is effectively the same value; so it's true for all week values. And that is how, you would get summation for all week values instead of max week value.

I know that I am not good at explaining..still hope this would help.

[JordyWegman]

This explains it very clearly and sounds also very logical, looking at the way it get evaluated. Thank you for this knowledge!

Jordy

Climber