Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try '>=' & Date(Today()-7) & '<=' & Date(Today)
Try '>=' & Date(Today()-7) & '<=' & Date(Today)
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.
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))
];
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()))
This formatting worked immediately when I put it into my script. Thank you for your help!
You're welcome. Glad you got it sorted.