Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
joseward1
Contributor III
Contributor III

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

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.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

6 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
swuehl
MVP
MVP

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
MVP
MVP

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
MVP
MVP

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()))

joseward1
Contributor III
Contributor III
Author

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.


talk is cheap, supply exceeds demand