6 Replies Latest reply: Jul 12, 2011 5:10 AM by Stefan Wühl

# Set Analysis  - Dates

Hi,

I have the below expression. This expression returns me zero. Not sure where I am wrong.

\$(varPreYearFullDateEnd) has been assigned =Date('31/12/2010','DD/MM/YYYY'). I want to do the calculation for 31/01/2011 by adding 1 month.

On the other hand.

=Count({<[link_claim_Claim_Status]={"*"} - {"CLO"},[Start Date] ={"<=\$(varPreYearFullDateEnd)"}>} DISTINCT [ClaimID]) just works fine.

returns me the correct value. I just want to add 1 month. I tried many ways where I will have date in the number format, use sum etc..finally asking the community to help.

Rajesh Vaswani

• ###### Set Analysis  - Dates

Hi Rajesh Vaswani,

you need a dollar sign expansion also for the addmonths function to call it within the set expression:

Regards,

Stefan

• ###### Re: Set Analysis  - Dates

Hi Stefan,

Thankyou for your reply. After \$ expansion too it does not work. I am trying to figure out why. Let me know if you could suggest anything?

Thanks,

Rajesh Vaswani

• ###### Re: Set Analysis  - Dates

The following expression seems to work

• ###### Re: Set Analysis  - Dates

Hi Rajesh Vaswani,

I noticed two issues:

with variable definition like

=Date('31/12/2010','DD/MM/YYYY')

I needed to remove the \$() around the variable name when using the variable as argument to AddMonths function, i.e.

(I also struggle from time to time with variable evaluation).

It is also important, that there is no space between the smaller than (<=) and the dollar sign of the dollar sign expansion (I get zero as result otherwise).

As Erika wrote, I think a MakeDate would be clearer to understand what you want to achieve (assigning a date to a variable), so your expression should work as well.

In my test app, it works quite well, if you have any more problems, it may be useful if you could post a small sample application.

Regards,

Stefan

• ###### Re: Set Analysis  - Dates

Hi Rajesh,

Stefan is right and using dollar sign expansion should do the trick. Although with these kind of requirement I generally prefer to load the master calendar table in the load script. I had attached an example of master calendar for your reference. If you look into the master calendar you will see I have MTD, WTD, YTD & previousYear etc. Also, if I am re-using same expressions within other objects then I would declare my expression as variables and use the dollar sign expansion. This approach helps me to maintin the calculations in one centralised location (Variable Overiew Window). And the re-usability & maintainence of these expression is easy. This is just like adding an abstraction layer.

I hope this helps!

Cheers - DV

• ###### Set Analysis  - Dates

Accrding to me Variable should be assigned as:

varPreYearFullDateEnd=  assign it : =makedate('2010','12','31')