topic Re: How to retrieve Yesterday's info using an expression in a text box? in QlikView

How to retrieve Yesterday's info using an expression in a text box?

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

Hey everyone,

I am trying to get yesterday's data from my database and the text box stays empty. I can hard code the date and it works fine, but I need this to be automated and having some trouble using the Today()-1 function.

Sum({$<Date_Dash={(Date('Today() -1')},Program_Dash={'Mechanical Inspection'},Status_Dash={'Completed'}>} Processed_Dash)

I have also tried creating a variable in Main and using it in the 'Date(Today()-1)' place as well, no results.

Thanks!

Re: How to retrieve Yesterday's info using an expression in a text box?

vishsaggi — Thu, 29 Jun 2017 19:55:01 GMT

May be try this?

= Sum({$< Date_Dash = {"$(= Date(Today()-1, 'MM/DD/YYYY'))} , Program_Dash = {'Mechanical Inspection'}, Status_Dash = {'Completed'} >} Processed_Dash)

Assumed Date_Dash date format is MM/DD/YYYY.

Re: How to retrieve Yesterday's info using an expression in a text box?

joshrussin — Thu, 29 Jun 2017 20:38:48 GMT

Unfortunately, that gives me nothing too. I could either get a 0 answer or a blank return.

Re: How to retrieve Yesterday's info using an expression in a text box?

vishsaggi — Thu, 29 Jun 2017 20:49:55 GMT

Can you try adding = in the Date(Today ) like:

= Sum({$< Date_Dash = {" = $(= Date(Today()-1, 'MM/DD/YYYY'))} , Program_Dash = {'Mechanical Inspection'}, Status_Dash = {'Completed'} >} Processed_Dash)

Can you show me the format of your Date_Dash field?

Re: How to retrieve Yesterday's info using an expression in a text box?

joshrussin — Thu, 29 Jun 2017 20:57:43 GMT

Adding the = did nothing for me in the equation. Still returns nothing. thank you for trying to help me out!

Re: How to retrieve Yesterday's info using an expression in a text box?

vishsaggi — Thu, 29 Jun 2017 21:03:54 GMT

So what is that your are expecting can you share a sample to what you displayed in the table?

Re: How to retrieve Yesterday's info using an expression in a text box?

joshrussin — Thu, 29 Jun 2017 21:13:22 GMT

The end goal of this would be to have a text box for each of the following:

The first ones up top are just labels, output text boxes are separated a little.

(Lets say today is 6/2/2017, "WIP" is from yesterday)

Program_Dash Completed WIP

Loaners 164 122

Re: How to retrieve Yesterday's info using an expression in a text box?

vishsaggi — Thu, 29 Jun 2017 21:19:25 GMT

Try this? Sorry did not close the double quotes.

= Sum({$< Program_Dash = {'Mechanical Inspection'}, Status_Dash = {'Completed'},

Date_Dash = { " = $(= Date(Today()-1, 'M/D/YYYY'))" } >} Processed_Dash)

Re: How to retrieve Yesterday's info using an expression in a text box?

joshrussin — Thu, 29 Jun 2017 21:30:02 GMT

Now I am receiving a 0 instead of nothing.

Re: How to retrieve Yesterday's info using an expression in a text box?

vishsaggi — Thu, 29 Jun 2017 21:31:10 GMT

Time to share your app, it would be easy to work on can you upload your sample here?

Re: How to retrieve Yesterday's info using an expression in a text box?

joshrussin — Thu, 29 Jun 2017 21:51:37 GMT

How do I share?

Re: How to retrieve Yesterday's info using an expression in a text box?

vishsaggi — Thu, 29 Jun 2017 22:32:12 GMT

When you click on reply you can see Use Advanced Editor on right top of the reply window. Once you click on it you can use the attach link and share.

Re: How to retrieve Yesterday's info using an expression in a text box?

joshrussin — Fri, 30 Jun 2017 13:36:49 GMT

I think I did that right

https://us.qlikcloud.com/view/5956534fd3bba5000191bc16

Re: How to retrieve Yesterday's info using an expression in a text box?

jmvilaplanap — Fri, 30 Jun 2017 13:47:25 GMT

Today() function has parameters, maybe is something happening about this.

0 Date at script run

1 Date at function call

2 Date when the document was opened

Default timer_mode is 2. The timer_mode = 1 should be used with caution, since it polls the operating system every second and hence could slow down the system.

How does function Today() work? | Qlik Community

Re: How to retrieve Yesterday's info using an expression in a text box?

vishsaggi — Fri, 30 Jun 2017 13:47:43 GMT

Sorry did not get your what is this link again?

Re: How to retrieve Yesterday's info using an expression in a text box?

sunny_talwar — Fri, 30 Jun 2017 14:01:49 GMT

May be this

=Sum({$<Program_Dash={'Loaners'}, Status_Dash={'WIP'}, Date_Dash = {"$(=Date(Max(Date_Dash)-1))"}>} Processed_Dash)

Re: How to retrieve Yesterday's info using an expression in a text box?

joshrussin — Fri, 30 Jun 2017 14:49:13 GMT

That is most certainly helping! But I tried in my database and am getting a result, but it is not correct. Here is what I am getting with this line. The result I am getting is 1476.

=Sum({$<Program_Dash={'Loaners'}, Status_Dash={'WIP'}, Date_Dash = {"$(=Date(Max(Date_Dash)-1))"}>} Processed_Dash)

Re: How to retrieve Yesterday's info using an expression in a text box?

sunny_talwar — Fri, 30 Jun 2017 14:53:03 GMT

Seems like you might have duplicate row... may be this

=Avg({$<Program_Dash={'Loaners'}, Status_Dash={'WIP'}, Date_Dash = {"$(=Date(Max(Date_Dash)-1))"}>} Processed_Dash)

Re: How to retrieve Yesterday's info using an expression in a text box?

joshrussin — Fri, 30 Jun 2017 14:58:55 GMT

You were correct, I had the data also being loaded into Main. I took that out and it is now correct! Thank you for your help!

Re: How to retrieve Yesterday's info using an expression in a text box?

sunny_talwar — Fri, 30 Jun 2017 15:05:36 GMT

Awesome