How to check in set statement whether date falls withing interval of dates

[Sagar082]

Hi folks,

Greeting for the day. I would like to create charts which can show the details of measures between interval, for e.g.  if user input a date 01/16/2019 then the detail should be reflect for the period 01/09/2019 and 01/16/2019 and that for the period 01/16/2019 and 01/23/2019.

I'm using two charts to make comparison, one for previous week and other for next week.

I've created two variables vNext_Dt and vPrev_Dt, I included following description assuming that it will return me date;

vPrev_Dt = $(vDate) - 7

vNext_Dt = $(vDate) + 7

whereas, vDate is the date inputted by  user.

But, when I am testing the output of both the variable, I am getting output as; -7 and 7 instead of date.

Also, I'm using following set expression in measure to get desire result;

count({ < Date = {'>=$(vPrev_Dt)<=$(vDate)'}>} variable_name)

The above set statement should return the count of variables_name whose Date falls among vPrev_Dt and vDate.

Can someone please help me to rectify variable output and also, analyse and guide me about the set statement whether I use correctly or not.

If there is any other solution, I will welcome it.

Regards,

Sagar.

[jwjackso · Accepted solution]

Let vDate='01/16/2019';

Let vPrev_Dt=Date(Date#('$(vDate)','MM/DD/YYYY')-7,'MM/DD/YYYY');

Let vNext_Dt=Date(Date#('$(vDate)','MM/DD/YYYY')+7,'MM/DD/YYYY');

[Sagar082]

Hi jwjackso,

Greeting for the day. Firstly thank you for the solution. Secondly, I would like to ask few things, I believe you don't mind it.

You used below code in the script;

Let vDate='01/16/2019';

Let vPrev_Dt=Date(Date#('$(vDate)','MM/DD/YYYY')-7,'MM/DD/YYYY');

Let vNext_Dt=Date(Date#('$(vDate)','MM/DD/YYYY')+7,'MM/DD/YYYY');

I would like to know about the Let; does it create variable? or I have create variable first.

Secondly, I have use below set statement in measure, but not giving desire result.

count({ < Date = {'>=$(vPrev_Dt)<=$(vDate)'}>} variable_name)

Expectation; the set statement should result the count of variable whose date is falling among two dates, inclusive.

Unfortunately, I have only two dates, 31st Dec 2019 and 1st Jan 2019.

But, still I think it is not working, as I try to test in following manner;

count({ < Date = {'>=$(vPrev_Dt)'}>} variable_name)

where I set the user input date to 01/08/2019, still I'm not getting any result, till I will change the condition to;

count({ < Date = {'=$(vPrev_Dt)'}>} variable_name)

I'm keen to know whether my expression correct in manner of syntax and logic.

Please do advise.

Regards,

Sagar.

[jwjackso · Accepted solution]

The Let  and Set statements create variables.  The Let statement evaluates the expression to the right of the equal sign before assigning the result to the variable.  The Set statements assigns the expression to the variable.

I would expect the count({ < Date = {'>=$(vPrev_Dt)<=$(vDate)'}>} variable_name) statement to function as you described.  In the expression editor, check the formula at the bottom of the editor to see how the variables are expanded.  Since this is a date comparison, verify that the variable are in the same format as Date.

[Sagar082]

Hi,

Greeting for the day. Thank you for the reply and brief about the Let and Set statement, as it cleared my ambiguity.

Let me try about the set analysis. I felt that I wrote right, but it was not producing result as expected, so I want to verify it.

Let me push more data and analyse the same.

Bye the way, thank you for your prompt reply.

Regards,

Sagar.