Lastdate -1 filter

[jacek27031]

Hi there, 

how to calculate a measure with maxstring -1, -2, -3 etc filter?

My basic code seems to be working:

Sum({<Week2={'$(=maxstring(Week2))'}>} [#SO Act])

I've tried like this but no effect. 

Sum({<Week2={'$(=maxstring(Week2))'}>-1} [#SO Act])

Thanks for any help!

[RafaelBarrios · Accepted solution]

Hi @jacek27031 

a few things here

First
For numbers or dates you should be using max() and not MaxString()

Second
When you do sum({<data set>-1} field) is like you're subtracting sets, so it would be like subtracting the entire dateset '1' to your custom set '<data set>', which would result in null

Third
by default, max() will return the very max, and if you add a second parameter it can return the second max, third max, and so on.

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/Scripting/Aggrega...

for example:

for weeks: 1,2,3,4,5,......,18,19,20

max(weeks) or max(weeks,1) will return 20
max(weeks,2) will return 19
max(weeks,3) will return 18
and so on

so, your expression should be something like

Sum({<Week2={'$(=max(Week2,2))'}>} [#SO Act])

Hope this helps
best,

Help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 🙂

[jacek27031]

Thank you so much @RafaelBarrios !

[jacek27031]

@RafaelBarrios , and what if the "Week" column is non-numeric like "W01, W02, W03 ..."?

[RafaelBarrios · Accepted solution]

That would be a tricky one

i can’t try now, but I would try something like:

='W'&Max(num#(right(week2,2),'00'),2)

first try this in a text object to see the result

and then put it in your formula

Sum({<Week2={"$(='W'&Max(num#(right(week2,2),'00'),2))"}>} [#SO Act]))

or

Sum({<Week2={"=$(='W'&Max(num#(right(week2,2),'00'),2))"}>} [#SO Act]))

or

Sum({<Week2={'$(=''W''&Max(Num#(Right(Week2,2),''00''),2))'}>} [#SO Act])) 

double single quotes as scape character

I could have some syntax mistake with single or double quotes.

hope this answer your question

best,