Set Expression with Dates

[#1]

I don't think this should be too hard but having trouble getting the set expression to work:

I am trying to sum on a field called demand where the demand occurs in the current month + 1

For examples - I have a series of demand entries - time with different dates attached in the future.  I want to pull in the demand where the date is in March (Feb + 1). 

My fields are:

- DemandMonth - this is the month where the demand occurs

- Demand - this is the amount of hours

I have a master calendar that is linked to the date and creates the demandmonth field

[erichshiino]

What's the format of [Demand Month] or Month in your expression?

Sum({<[Demand Month] = {$(AddMonths(Month,1))

Sometimes, addmonths or other functions will reset the format to a date or timestamp.

You can try this:

Sum({<[Demand Month] = {$(=num(AddMonths(Month,1)))} > } Value)

If you try this fuction in a straight table without any label on the expression, you will be able to see what happend inside the {}. There, it should be like this:

Sum({<[Demand Month] = {3} >} Value)

Regards,

Erich

[#3]

Here is what I got in the label:  sum({<[DemandMonth] = {41306} > } Demand)

[erichshiino]

Ok

What do you have when you put the field DemandMonth in a listbox? It should be in the same format ( 41000, for example).

If it is not, you can set it in document settings -> number ( be careful to not include thousand separators or decimals).

Check if the value 41306 exists.

Regards,

Erich

[#5]

Erich,

I got some help from a QV resource and what we did was set up an environment variable in the settings for the current month, next month, etc. Then we referenced the variable in the set expression. This worked and solved the issue.

Thanks, David