Setting Variables

[danielnevitt]

Hi,

I am trying to set variables to allow us to add expressions to straight tables to show the sum of current month, previous month etc.

My column name is CONTRACT_MONTH

The variables I have set are:

vCurrMonth = =Max(CONTRACT_MONTH)

vLastMonth = =vCurrMonth-1

The above return numeric results ie vCurrMonth = 8 and vLastMonth = 7

My CONTRACT_MONTH data is in the format Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec.  I am summing this data by a column called CONTRACT_QTY.

Would it be possible for someone to advise firstly how I set the variables to the above format and secondly how I can show those results in a straight table similar to the below:

Desk          SumCurrMonth          SumLastMonth

A              

B

C

D

Any help would be much appreciated.

Regards,

Daniel

[Gysbert_Wassenaar]

If you have a field with month numbers you could use that instead. Or create one in the script and then use it. Or you could add two helper variables to get the month names from the numbers:

vCurrMonthName: =month(makedate(1,vCurrMonth))

vLastMonthName: =month(makedate(1,vLastMonth))

SumCurrMonth: =sum({<CONTRACT_MONTH={'$(vCurrMonthName)'}>}CONTRACT_QTY)

SumLastMonth: =sum({<CONTRACT_MONTH={'$(vLastMonthName)'}>}CONTRACT_QTY)

[danielnevitt]

Hi Gysbert,

Thank you for the reply.

The setting the MonthName works great.

Unfortunately the SumCurrMonth and SumLastMonth do not work.

The following expressions work (without variables):

sum({<CONTRACT_MONTH={`Jun`}>}CONTRACT_QTY)

sum({<CONTRACT_MONTH={`Jul`}>}CONTRACT_QTY)

Are you able to suggest how to insert the variables to the above?

Thanks

Daniel

[Gysbert_Wassenaar]

No idea. It works for me. See attached example.