Date comparisons with sum - Set analysis

[#1]

Hi,

Could someone tell me what is wrong with the expression below? I keep getting nothing coming out. When I create list boxes and charts I can get the correct information out but when I try to replicate my selections in an expression, it doesn't work.

MonthYear90 gives a value like Jan-09, Feb-09, Mar-09, etc... and MonthEnd-90 is a field displaying 90 days before the end of the current month.

The calculation should bring back the sum of the amount where Posting Date <= last day of current month and Document Date >= 90 days before the end of the current month.

Thanks in advance,

Conor

Sum({< [BSID90.Posting Date_BUDAT] = {"<=$ (=date(MonthEnd(MonthYear90)))"}, [BSID90.Document Date_BLDAT] = {">= (=date([MonthEnd-90]))"} >} BSID90.Amount_WRBTR * [BSID90.BSID Debit/Credit_FLAG])

[Miguel_Angel_Baeyens]

Hello Conor,

First, make sure that the field [BSID90.Posting Date_BUDAT] has the same date value that the one returned by Date() function over your MonthYear90 and MonthEnd-90.

Second, you may need to do a bit more trasnformation to get proper formatted dates

MonthEnd(Date(Date#(MonthYear90, 'MMM-YY')))

will return something like "30/06/2009". So you will get a final expression like

Sum({< [BSID90.Posting Date_BUDAT] = {">=$(=date([MonthEnd-90]))<=$(=MonthEnd(Date(Date#(MonthYear90, 'MMM-YY')))"} >} BSID90.Amount_WRBTR * [BSID90.BSID Debit/Credit_FLAG])

Besides, you can create a master calendar where you get one record per date field (say Posting Date), with as many fields as you need, for example

AddMonths(MonthEnd([Posting Date]), -3) AS CompleteDate-90,Date(AddMonths(MonthEnd([Posting Date]), -3), 'MMM-YY') AS MonthYear-90

as well as any other dimension you will use in your charts.

Hope that helps.

[#3]

Hi Miguel,

Thanks for getting back so quickly. I checked that all the dates are in the same format - they are.

Here is my new code but it is bringing back zero amounts:

Sum({< [BSID90.Posting Date_BUDAT] = {"<=$(=MonthEnd(Date(Date#(MonthYear90, 'MMM-YY'))))"}, [BSID90.Document Date_BLDAT] = {">=$(=date(monthend(MonthYear90) - 90))"} >} BSID90.Amount_WRBTR * [BSID90.BSID Debit/Credit_FLAG])

As you can see from the screenshot it doesn't seem to be looking at anything when comparing the Posting and Document dates. any ideas?

Conor

[Miguel_Angel_Baeyens]

Hi,

As an idea, try those expressions in a text object, select a date and see what results are displayed in it. It's not evaluating the MonthEnd() nor the date() expressions, so you first need to check that both functions return the right values.

Hope that helps.

[#5]

Miguel,

When you select only one value for MonthYear90 (eg: Aug-09) it works and displays the correct values in text boxes and in the chart. But when there is more than one value selected or when there are none it displays zeroes.

So how do I get the chart to display all values?

Conor

[#6]

You can check the Always one selected value on the date field you're using.

Or instead of doing a set analysis using Min or Max of your MonthYear90

As an example "<=$(=Max(MonthEnd(Date(Date#(MonthYear90,'MMM-YY')))))"

Rgds,

Sébastien