How do you include a date range in a data island?

[Anonymous]

I am using a data island to define: Day, Week, Month and use a list box set to the data island to toggle my graphs and text objects to change based on what has been selected. Currently I am able to include a specific date in the script, but I can't quite figure out how to include a date range.

My declaration of the data island is:

LOAD * INLINE [
  %Metric, Exp
  Day,   Date(Today()-1)
  Week,  Date(Today()-7)
  Month, Date(Today()-30)
];

The Exp column is used in text objects to change the outputs. An example of one of them is:

=count(DISTINCT{$<CreatedOn = {"$(=$(=Exp))"}>}SessionID) 

This counts the distinct number of SessionIDs and outputs the number in the text object. And this is where I am having problems getting the range to work, because with charts and graphs I just check conditional and re-write the expression based on whats selected.

I've tried declaring the Exp a few different ways:

Month, CreatedOn={"<=$(=Date(Today()))>=$(=Date(Today()-7))"} 
Month, {"<=$(=Date(Today()))>=$(=Date(Today()-7))"}
Month,<=$(=Date(Today()))>=$(=Date(Today()-7))

If one of these is the correct way, then the problem I am having is calling it in the text object.

I hope I am clear enough in what I am asking. If there is something that might need rewording, let me know and I will do my best to clear it up.

[Gysbert_Wassenaar · Accepted solution]

Try '>=' & Date(Today()-7) & '<=' & Date(Today)

[swuehl]

I assume a problem could be the dollar sign expansions alread getting expanded (to NULL) in your script execution.

Check after reload, if the Exp field actually shows the correct strings.

If not, I think the easiest way would be to load the table from an excel file, not from an INLINE table.

[swuehl]

Another option using the INLINE table uses Replace():

LOAD %Metric, Replace(Exp,'@','$') as Exp INLINE [

%Metric, Exp

Month, CreatedOn={"<=@(=Date(Today()))>=@(=Date(Today()-7))"}  

Month, {"<=@(=Date(Today()))>=@(=Date(Today()-7))"} 

Month,<=@(=Date(Today()))>=@(=Date(Today()-7)) 

];

[swuehl]

And just for clarification, you should use only a single Exp per distinct %Metric value, so this

LOAD %Metric, Replace(Exp,'@','$') as Exp INLINE [

%Metric, Exp

Month, CreatedOn={"<=@(=Date(Today()))>=@(=Date(Today()-7))"} 

Month, {"<=@(=Date(Today()))>=@(=Date(Today()-7))"}

Month,<=@(=Date(Today()))>=@(=Date(Today()-7))

];

won't work in your application, you need distinct %Metric values

LOAD %Metric, Replace(Exp,'@','$') as Exp INLINE [

%Metric, Exp

Month1, CreatedOn={"<=@(=Date(Today()))>=@(=Date(Today()-7))"} 

Month2, {"<=@(=Date(Today()))>=@(=Date(Today()-7))"}

Month3,<=@(=Date(Today()))>=@(=Date(Today()-7))

];

But I assume you've just posted three alternatives for the same final Month line?

This one should work with your original Count() expression:

Month, >=@(=Date(Today()-7))<=@(=Date(Today()))

[Anonymous]

This formatting worked immediately when I put it into my script. Thank you for your help!

[Gysbert_Wassenaar]

You're welcome. Glad you got it sorted.