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]

Here's an image of the result:

Term	MonthEnd(Max(TOTAL ReturnDate),-(Term-1))	Count({$<ReturnDate={">="}>} Return)
		74
1	2013-03-31	74
3	2013-01-31	74
6	2012-10-31	74
12	2012-04-30	74
36	2010-04-30	74
60	2008-04-30	74

[Anonymous]

Try this:

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

This is a formula that works in my applications:

    sum({$<TransDate={'<=$(BSDurationMEnd)'}>} NLValue)

Make sure the date is  a date though. If you're not sure try creating a second variable that converts it using Date($(vDate)).

Jonathan

[shreyashetty_ge]

Hi,

Firstly check the date formats whether it is same throughout i.e the variable in which date is stored and the one which you are comparing, in any text object.

Then try this below set analysis .

Count({<ReturnDate={">=$(vDate)"}>} ReturnDate)

Hope it works.

[Anonymous]

Firstly, apologies for jumping from vStartDate to vDate. They are in fact the same and this is a typo.

Your suggestions don't work. I think the vStartDate is a date because it formats as one if I leave the formatting as expression default. If I change the formatting to Integer, it shows as a number (as per table below). The first column is just Term. The second is vStartDate (shows nothing). The third and fourth are $(vStartDate) formatted differently (third is default, and fourth is integer). The last column is your suggested formula i.e. Count({$<ReturnDate={'>=$(vDate)'}>} ReturnDate)

It has now changed from 72 to 0 (I don't know if that helps).

Thanks again.

Term	vStartDate	MonthEnd(Max(TOTAL ReturnDate),-(Term-1))	MonthEnd(Max(TOTAL ReturnDate),-(Term-1))	Count
				0
1		2013-03-31	41365	0
3		2013-01-31	41306	0
6		2012-10-31	41214	0
12		2012-04-30	41030	0
36		2010-04-30	40299	0
60		2008-04-30	39569	0

[Anonymous]

Sorry, also does not work. Could I be defining my variables wrongly i.e. do I need '=' in them. Perhaps my definitions above were badly stated, but it does seem to produce the correct dates in theother columns. It is somehow just not doing the set analysis with these dates correctly.

Any other suggestions?

Thanks.

[jagan]

HI,

Try like this

=Count({<ReturnDate={">=$(=vDate)"}>} ReturnDate)

If you need distinct date then use
=Count({<ReturnDate={">=$(=vDate)"}>} DISTINCT ReturnDate)

Before that check the date formats in date dimension and variable are same.

Hope this helps you.

Regards,

jagan.

[Anonymous]

Sorry, but still doesn't work.

How do I check date formats in dimension and variable? They are numbers representing dates i.e. get formatted as dates when displayed but internally stored as numbers (see above).

Will it help if i leave out the day i.e. just compare to YYYY-MM?

Thanks.

Joao

[Anonymous]

How about wrapping a date() function around the vDate ?

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

Jonathan

[Anonymous]

I have tried that with no effect.

I can't understand why this is so difficult when it should be so easy. I must be doing something wrong.

Both my ReturnDate and my variables are definitely dates (numbers), not text! Is this the problem?

Thanks.

Joao.