Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
shinnickr
Creator II
Creator II

Set a variable to include everything up to yesterday's date.

Hi,

I am hoping to set a variable to include every day up to yesterday.  My field starts in 2012 and I want to include every day from 2012 -  yesterday.  I have a variable for yesterdays date 'Date(Today() -1)' but I want to include everything up to and including that date.  I don't know if I'll need some set analysis or if there's a function that would help me with that so I would appreciate any help that can be given.

Thank you very much.

1 Solution

Accepted Solutions
sunny_talwar

This worked

='=[Service Date] < Today()'

View solution in original post

42 Replies
vishsaggi
Champion III
Champion III

What is the expression you are using to implement this. May be create two variables like below and use them in your set analysis.

Capture.PNG

Sum({< DateField = {">=$(v2012) <= $(vYesterday)" } >} Yourdimfield) something like this.

shinnickr
Creator II
Creator II
Author

Thanks!  I will use this and give it a shot and work on it from there.

sunny_talwar

You might be missing equal signs here:

Sum({<DateField = {">=$(=v2012) <= $(=vYesterday)"}>} Yourdimfield)

i_bagnoli
Creator
Creator

Hi Ryan,

what are you looking for, exactly?

If you need a filter in script, you can use something like:

     let vYesterday=today()-1;

     load *

     from [...]

     where Date<=$(vYesterday);

On the other hand, if it is a layout matter, you can use set analysis in a chart or table, for ex.: Date as Dimension, sum({<Date={"<=$(vYesterday)"}>} Field) as Expression.

I hope it helps.

IB

vishsaggi
Champion III
Champion III

Oops thanks Sunny. Got it.

shinnickr
Creator II
Creator II
Author

Thanks for the help.

I got my analysis sort of working, but not with the intended output.

I am using : =Date(Sum({< [Service Date] = {">=$(=v2012) <= $(=vYesterdaysDate)"}>} [Service Date]), 'YYYY-MM-DD')

and using a list box to just show my options I get:

FunkyDate.PNG

So I don't think I want a sum of all the dates, I just want to list the dates.

sunny_talwar

"Using a list box to just show my options I get:"???? Not sure I understand this? What are you expecting to see?

shinnickr
Creator II
Creator II
Author

Hi Irene,

Thanks for the help.

I do need this to be with set analysis in a chart or table.  I have tried going forward with what you'd suggested and got to =date(sum({<[Service Date]={"<=$(vYesterdaysDate)"}>} [Service Date]))

When I do that, though, I get a single date, not a list, of 09/13/13381567.  Pretty big year.... Not sure what's happening there.

shinnickr
Creator II
Creator II
Author

I am just using a list box to show my current options for the expression.  So what I'm hoping to see is all my dates listed from 2012 up to yesterday.  I was just using it as a visual tool to see if my expression was working.

For instance, if I make a list box with my date field I'm working with [Service Date] I get:

Dates.PNG

This is every date listed out, including dates I don't want.

What I was hoping to see with the list box with my expression in it is all the dates from 2012 to the current date.