set analysis based on different values of the same field.

Fri, 30 Mar 2012 08:21:20 GMT

Hi,

I have a sales plan figure for each month of 2012.

First I want to see the planned sales for the next coming months, as in a total.

First, I wrote a variable which takes the current month : vCurrMonth = Num(Month( Today() ))

Then I wrote a listbox to test whether it works to have the months I want

=if(Month>=vCurrMonth and Month<vCurrMonth+3,sum(planned))

So this works.

Now I need a more complex situation, I want to see whether we will be able to reach that value, into one figure :

total (sum next 3 planned months) - sum(contracts) - sum(orders) - sum(this months issued so far).

Building this formula is not an issue, but I need something that does the same as the listbox expression,

Month>=vCurrMonth and Month<vCurrMonth+3, but as in a set analysis expression.

I dont know how to make a set analysis based on multiple values from the same field

As output, I just want to have one figure, the total, for example -56.000.

I made it work for the current month :

=sum({$< Month = {$(vCurrMonth)} >} vOutput)

How can I add the othe months vCurrMonth + 1 and vCurrMonth + 2 into this set analysis?

I think this is a newbie question, sorry for this.

CELAMBARASAN — Fri, 30 Mar 2012 08:54:27 GMT

Hi,

Check with this

Sum(Total {<Month={">=$(vCurrMonth) <$(vCurrMonth+3)"}>} Planned)- sum(contracts) - sum(orders) - sum(this months issued so far)

Celambarasan

jagan — Fri, 30 Mar 2012 09:55:57 GMT

Hi,

Try this

Sum({<Month={'>=$(vCurrMonth+1) <=$(vCurrMonth+3)'}>} Total Planned)- sum(contracts) - sum(orders) - sum(this months issued so far)

Hope this helps you.

Regards,

Jagan.

topic set analysis based on different values of the same field. in QlikView