how do i make this expression?

[#1]

i have a table like this:

month,value

1,234

2,434

3,212

4,1004

5,4443

in a textbox i want the sum of all values .. but only the MAX(Month)-1 till MAX(Month-3) .. dynamic

[johnw]

I think this:

sum({<month={'>=$(=max(month)-3) <=$(=max(month)-1)'}>} value)

[#3]

that gives me a 0 as result

[#4]

It seemed to work for me, I got 1,650.

[#5]

your expression dont have colors .. when i copy/past the colors .. then i have colors in the expression

[#6]

what version are you using? .. seems like 8.5 is working . 9.0 RC not

[johnw]

Yeah, it might be a version problem. The expression I gave will only work in 8.5 or above. Below that, either of these should work:

=-sum((month>=$(=max(month)-3) and month<=$(=max(month)-1))*value)

=sum(if(month>=$(=max(month)-3) and month<=$(=max(month)-1),value))

The first should perform better, the second might be more clear.

(Edit: More specifically, the expression is working fine for me in versions 9.00.7119.4 and 8.50.6231.5. Those are the ones I can easily test.)

[stephencredmond]

Hi,

What happens if Max(month) is equal to February?

😉

Stephen

[johnw]

Then you'll get January only, which may or may not be what you wanted. It isn't clear from the original question. I've assumed this was an example question in search of an example answer, and not "here is my real application and my real data".

In a real application, that we're looking for a given month and three months previous to me implies that our month field should actually be a date instead of a mere month number, probably the first day of each month, just displayed with a year and month. We would then be doing the subtraction using addmonths(month,-1) for instance, and would have to format the result the same as our month is formatted. Something like this:

sum({<month={">=$(=date(addmonths(max(month),-3),'MMM YYYY')) <=$(=date(addmonths(max(month),-1),'MMM YYYY'))"}>} value)

But since that wasn't the actual question, and doesn't solve the actual question posed, I figured I'd hold off on throwing that out there until I got a follow up question. 😉