Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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! 🙂
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,
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.
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! 🙂
Thank you so much @RafaelBarrios !
@RafaelBarrios , and what if the "Week" column is non-numeric like "W01, W02, W03 ..."?
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,