Declaring a date variable in the back end

[Ja123__]

Hi All

In the backend of my script, I have been declaring my two variables as follows:

Set vStartDate = '2019/10/01';
//monthstart(addmonths(today(),-1));

set vEndDate = '2019/10/31';
//monthend(addmonths(today(),-1));

I've been experimenting with different formulas but can't see why the variables that are declared don't want to work with my set analysis calcs? I believe this is just an error on my part, missing a step. I've tried added text and other formulas into the "month end " calc. Basically all i want is go back a month from today and then find the month start and end.

This is an example of how I have been building these calcs:

count( distinct

{

<dteTerminationDate3INDICATOR = {'-1'} , dteTerminationDate4INDICATOR = {'-1'} , dteDJFNew = {"<=$(vEndDate)"} ,dteDOXNEW = {">$(vEndDate)"}>
+
<dteTerminationDate3 = {">$(vEndDate)"} , dteTerminationDate4 = {">$(vEndDate)"} , dteDJFNew = {"<=$(vEndDate)"} ,dteDOXNEW = {">$(vEndDate)"}>
+
<dteTerminationDate3INDICATOR = {'-1'} , dteTerminationDate4 = {">$(vEndDate)"} , dteDJFNew = {"<=$(vEndDate)"} ,dteDOXNEW = {">$(vEndDate)"}>
+
<dteTerminationDate3 = {">$(vEndDate)"} , dteTerminationDate4INDICATOR = {'-1'} , dteDJFNew = {"<=$(vEndDate)"} ,dteDOXNEW = {">$(vEndDate)"}>

+

<dteTerminationDate3INDICATOR = {'-1'} , dteTerminationDate4INDICATOR = {'-1'} , dteDJFNew = {"<=$(vEndDate)"} ,dteDOXNEWINDICATOR = {'-1'}>
+
<dteTerminationDate3 = {">$(vEndDate)"} , dteTerminationDate4 = {">$(vEndDate)"} , dteDJFNew = {"<=$(vEndDate)"} ,dteDOXNEWINDICATOR = {'-1'}>
+
<dteTerminationDate3INDICATOR = {'-1'} , dteTerminationDate4 = {">$(vEndDate)"} , dteDJFNew = {"<=$(vEndDate)"} ,dteDOXNEWINDICATOR = {'-1'}>
+
<dteTerminationDate3 = {">$(vEndDate)"} , dteTerminationDate4INDICATOR = {'-1'} , dteDJFNew = {"<=$(vEndDate)"} ,dteDOXNEWINDICATOR = {'-1'}>

}

MemberID )

Kind regards,

James

[treysmithdev]

Easiest way is to use MakeDate.

Let vStartDate = MakeDate(2019,10,01);

Alternatively you could do:

Let vStartDate = Date(Date#('2019/10/01','YYYY/MM/DD'));

[marcus_sommer · Accepted solution]

There are three (potential) issues in your approach. One is your used format for the variables which is by calling the variable just a division-statement - means 2019/10/01 returned 201.9. The other is such a variable-content and the referencing fields must be recognized as date and without further measurements this happens only if the format is equal to the default date-format. And at last functions like monthstart() don't return a date else a timestamp.

Therefore I suggest to use only pure numeric values for each comparing/calculating with dates/times to avoid any problems with the formatting-stuff respectively the additionally efforts to get rid of them. This means in your case just:

Let vStartDate = num(floor(monthstart(addmonths(today(),-1))));

... <dteTerminationDate3 = {">$(vEndDate)"} ...

respectively

... <dteTerminationDate3Num = {">$(vEndDate)"} ...

Beside this your variables referenced not to any selection else they are with today() quite static and therefore it might be more suitable to make these checks already within the script - maybe in some flag-fields.

- Marcus

[Ja123__]

Hi Marcus

Thank you for taking the time to respond with your details. It is my default date which is set in the app but as for your other points I take note (especially using the date number) and need to test this out and revert.

Thanks again,
James