Set Analysis for beginning of month

[#1]

I have a set of calendar dates in a list box to allow a user to select a single date. If the user selects e.g. 04/02/2011 (4-FEB-2011), I would like to evaluate a set expression for the first of the month (1-FEB-2011).

The expression I have so far is:

Sum({$<[Date ID] = {$(=Min([Date ID]))},

[Month ID] = {$(=Max([Month ID]))},

[Calendar Date]=>} Qty)

This doesn't seem to work. When a user selects a [Calendar Date], the set of [Date ID] is restricted to just one date id and not the set of [Date ID] in the month so I can't find the minimum

My [Date ID] look like 20110201, 20110202 etc

My [Month ID] look like 1, 2 etc

My [Calendar Date] look like 01/02/2011, 02/02/2011 etc

What am I missing?

Thanks,

[maneshkhottcpl · Accepted solution]

Hi,

U can use the monthstart() function to resolve ur issue.

monthsatrt(date) gives u the 1 st date of month start.

=

Sum({$<[Date] = {"$(=monthstart([Date]))"}>} value)

Please refer the attached application.

Manesh

[#3]

Thanks, that worked, but I'm curious why you need the quotes around $(=monthstart([Date])) ?

[#4]

$ This shows the current value here it is month and

Monthstart will give the first date of that month.

[#5]

That doesn't really answer my question.

Why isn't

=Sum({$<[Date] = {$(=monthstart([Date]))}>} value)

sufficient?

Why do we need the quotes around the date?

[maneshkhottcpl]

Hi, Whenever we use the values with wildcard like * or function like abouve we need to use the Double quotes around it. But if we are using the single value like 2000 then we dont required. we can use for "20*" means 20XXX any value starts with 20.

Note - If my previous answer is right then mark as verify

Thanks

Manesh