Using Dates in Set Analysis

[Anonymous]

Hi

I'm trying to do something that I think would be fairly simple, but am failing horribly.

I want to use something like >=2010-01-31 in my date field. I have tried this with formulas and variables, using single and double quotes and equals signs all over the place inside and outside of brackets preceded by $ signs etc. Nothing seems to work.

I have a single dimension called Term, that determines how far back I look (with respect to the current date) in terms of data to include in my sum.

I start with vCurrentDate = Max(Total ReturnDate). I then create vStartDate = MonthEnd($(vCurrentDate),-(Term-1)).

If I then use the following expression: Count({$<ReturnDate={"$(='>='&$(vDate))"}>} ReturnDate), I get the same value for all Terms (where Term = 1,3,6,12,36,60 say).

Where am I going wrong? Is it my date format i.e. YYYY-MM-DD that is creating problems in Set Analysis?

Any help would be appreciated.

Thanks.

Joao.

[Anonymous]

I've also had problems in the past, especially with balance sheet budgeting calculations.

Try taking the > sign away to see if a straight equals test return anything.

   Count({$<ReturnDate={'=Date($(vDate))'}>} ReturnDate)

Jonathan

[shreyashetty_ge]

You already have come close to the solution,  its your date format which is creating problm. The format of the Vdate and ReturnDate should be same.Also, if you are not using any '='  while creating the variable then you use '=' while using it in set analysis.

[Anonymous]

So how do I make the format the same, or how is it creating problems? Please can you suggest a solution?

Also, your third sentence doesn't appear to be correct. If I change the definition of vDate from MonthEnd(Max(TOTAL ReturnDate),-(Term-1)) to =MonthEnd(Max(TOTAL ReturnDate),-(Term-1)), it stops working altogether i.e. even $(vStartDate) doesn't produce a result.

Thanks.

Joao.

[Anonymous]

Hi

I've added an example file.

Thanks.

Joao.