Dynamic Arrays - Sets

Thu, 29 Sep 2011 21:30:43 GMT

I’m trying to annualize some data and I have this expression:

= 12*(Sum( {$1<Year = {2011}>} Volume) / (vArchiveMonth))

I’m summing up all of the volume up to “today” and dividing it by the current archive month (kind of like an accounting “book” date) and then multiplying by twelve. The variable, vArchiveMonth is set at load time but can change at runtime.

But sometimes people add data forward – into the next month beyond the archive month. So I want to limit the numbers I sum() to only include data up to the archive month. So I tried this:

= 12*(Sum( {$1<Year = {2011}, NMonth <= {$(vArchiveMonth)}>} Volume) / (vArchiveMonth))

(NMonth is a numeric month field and I want it to be less-than-or-equal-to the archive month).But that's no good because I need a "Set" for Month to equal. -- I guess. So I tried this:

= 12*(Sum( {$1<Year = {2011}, NMonth = {$(vMos)}>} Volume) / (vArchiveMonth))

where vMos is a variable = 1,2,3,4,5,6,7 (just going up to July right now.)

And it works! Hooray! But it sucks! Boo! Way too much hard coded stuff is going on there.

I'm sure I can figure out how to deal with the Year but how do I limit the number of months?

One way is to be able to set and change the value of vMos at runtime. And it should be relative to vArchiveMonth. something like this:

for (int i = 1; i <= vArchiveMonth; i++) vMos += i; // needs to be calulated at runtime because vArchiveMonth can change during.

Or, I could try something completely different if anyone would like to suggest it.

thanks. -Erik.

Dynamic Arrays - Sets

swuehl — Thu, 29 Sep 2011 23:31:54 GMT

I think you are almost there, you could use search expression within the set expression to limit your month:

= 12*(Sum( {$1<Year = {2011}, NMonth = {"<=$(vArchiveMonth)"} >} Volume) / (vArchiveMonth))

Hope this helps and it works,

Stefan

Dynamic Arrays - Sets

Fri, 30 Sep 2011 03:10:25 GMT

Thanks. I thought I had tried that but I must have had a typo in it somewhere. Thanks again. That works.

-Erik.

topic Dynamic Arrays - Sets in QlikView

Dynamic Arrays - Sets

Dynamic Arrays - Sets

Dynamic Arrays - Sets