topic Re: Set a variable to include everything up to yesterday's date. in QlikView

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

shinnickr — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

vishsaggi — Mon, 30 Oct 2017 14:52:47 GMT

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

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

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

shinnickr — Mon, 30 Oct 2017 14:55:42 GMT

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

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

sunny_talwar — Mon, 30 Oct 2017 14:57:47 GMT

You might be missing equal signs here:

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

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

Anonymous — Mon, 30 Oct 2017 14:59:45 GMT

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.

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

vishsaggi — Mon, 30 Oct 2017 15:05:58 GMT

Oops thanks Sunny. Got it.

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

shinnickr — Mon, 30 Oct 2017 15:47:50 GMT

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:

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

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

sunny_talwar — Mon, 30 Oct 2017 15:56:37 GMT

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

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

shinnickr — Mon, 30 Oct 2017 15:58:15 GMT

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.

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

shinnickr — Mon, 30 Oct 2017 15:59:33 GMT

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:

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.

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

vishsaggi — Mon, 30 Oct 2017 16:01:10 GMT

Can you share your sample app and what is your expected output?

IF its a list box just use like below in your listbox expression

= IF(DateField >= $(v2012) AND DateField <= $(vYesterday), DateField)

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

sunny_talwar — Mon, 30 Oct 2017 16:01:28 GMT

Use text box object rather then list box

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

shinnickr — Mon, 30 Oct 2017 16:03:57 GMT

I still get the same thing. A date of '13515664-06-18'.

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

vishsaggi — Mon, 30 Oct 2017 16:05:33 GMT

Can you share your expression? Do not use Sum in your expression use IF. As mentioned in my earlier message.

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

shinnickr — Mon, 30 Oct 2017 16:14:00 GMT

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

And I am just using the list box as a visual tool to see if my expression is working. I apologize for being unclear.

My end-goal is to have a filter that selects every day from 'min(DateField) to Date(Today() -1)'. That's what I'm working towards. I was using the list box just to see if my expression was working the way I'd intended. Perhaps I should have been going about it a different way.

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

vishsaggi — Mon, 30 Oct 2017 16:17:57 GMT

You cant use listbox to sum the value. If you want to use sum function you have to use Textbox as Sunny mentioned in his earlier reply. If you want to see the dates use IF as i mentioned. Sum() or Count() for those dates can be displayed beside when you use expression tab and write an expression in that listbox. like here

What are you expecting in your listbox? All the dates just use IF condition and check.

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

shinnickr — Mon, 30 Oct 2017 16:24:52 GMT

What I'm hoping to see in my list box is all the days from min(DateField) to yesterday's date. I have dates in my DateField that are in the future currently (like up to 2018), and I do not want those in the selection. I am trying to set a filter that only shows from the beginning of the data to yesterday's date.

I tried using

=IF

([Service Date] >= MIN([Service Date])

AND

[Service Date] <= $(vYesterdaysDate), [Service Date])

but I was not getting anything returned.

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

sunny_talwar — Mon, 30 Oct 2017 16:26:32 GMT

Try this

If([Service Date] < Today(), [Service Date])

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

vishsaggi — Mon, 30 Oct 2017 16:28:55 GMT

Yes your will not cos the format i different when you use min. So use like

=IF ([Service Date] >= Date(MIN([Service Date]), 'YYYY-MM-DD'),

AND

[Service Date] <= '$(vYesterdaysDate)', [Service Date])

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

shinnickr — Mon, 30 Oct 2017 16:29:11 GMT

Perfect. Now I'm seeing exactly what I wanted to see in my list box.

Now how do I turn that into something I can use as an OnActivateSheet trigger to select all of those dates?