topic how do i make this expression? in QlikView

how do i make this expression?

Tue, 14 Jul 2009 03:10:09 GMT

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

how do i make this expression?

johnw — Tue, 14 Jul 2009 03:15:11 GMT

I think this:

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

how do i make this expression?

Tue, 14 Jul 2009 03:21:46 GMT

that gives me a 0 as result

how do i make this expression?

Tue, 14 Jul 2009 03:26:29 GMT

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

how do i make this expression?

Tue, 14 Jul 2009 03:32:19 GMT

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

how do i make this expression?

Tue, 14 Jul 2009 03:34:36 GMT

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

how do i make this expression?

johnw — Tue, 14 Jul 2009 03:37:56 GMT

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.)

how do i make this expression?

stephencredmond — Tue, 14 Jul 2009 04:20:09 GMT

Hi,

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

😉

Stephen

how do i make this expression?

johnw — Tue, 14 Jul 2009 05:20:49 GMT

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. 😉