topic Re: How to truncate date list box at today's date in QlikView

How to truncate date list box at today's date

Thu, 10 Apr 2014 13:19:50 GMT

Hi,

I have a list box in my sheet for date (MM/DD/YYYY). Selecting dates on this box slices the model.

I have the dates there sorted in descending order, and the problem is I see future dates there on top. I have records with dates all the way up to 2016 and that's of no use for me...

How do I get the list box to show dates only up to current day? (that is, not today, April 10th, but the day the sheet is accessed).

Thank you!

Re: How to truncate date list box at today's date

yduval75 — Thu, 10 Apr 2014 13:32:37 GMT

I think the best is to create other time fields like Year, Month in your script . Month(Date) as Month, Year(Date) as Year.

Then, the user could select 2014 and april and see only the dates for this selection by sorting the list box by descending order and state.

Re: How to truncate date list box at today's date

MK_QSL — Thu, 10 Apr 2014 13:36:11 GMT

In the List Box Expression... write down below

If(Date <= Today(), Date)

Re: How to truncate date list box at today's date

sudeepkm — Thu, 10 Apr 2014 13:41:52 GMT

How do I get the list box to show dates only up to current day? (that is, not today, April 10th, but the day the sheet is accessed).

When you say the day the sheet is Accessed does that mean the last time the document was opened or the last time the Data were loaded like document reload time() ?

Otherwise it will always be the same date as of Today. Please clarify.

Re: How to truncate date list box at today's date

Thu, 10 Apr 2014 13:46:14 GMT

Hi,

I mean today, as in =today() in excel.

Re: How to truncate date list box at today's date

MK_QSL — Thu, 10 Apr 2014 13:47:22 GMT

Try the replied I have given..

Re: How to truncate date list box at today's date

Thu, 10 Apr 2014 13:52:04 GMT

hi,

this doesn't achieve the needed result. This is what I get:

This is what I want:

Re: How to truncate date list box at today's date

Thu, 10 Apr 2014 13:52:57 GMT

see my comment please

Re: Re: How to truncate date list box at today's date

MK_QSL — Thu, 10 Apr 2014 13:54:40 GMT

Check enclosed file...

Re: How to truncate date list box at today's date

Thu, 10 Apr 2014 14:00:21 GMT

thanks for this.

want to hear something strange? even though the model was updated today, and even after I hit "clear", it still only goes up until April 8th, not 10th. And for sure I have data in my model from today and yesterday.

Re: How to truncate date list box at today's date

MK_QSL — Thu, 10 Apr 2014 14:02:25 GMT

You need to check Today() function....

today([timer_mode] )

Returns the current date from the system clock. The timer_mode may have the following values:

0 Date at script run
1 Date at function call
2 Date when the document was opened

Re: How to truncate date list box at today's date

Thu, 10 Apr 2014 14:05:36 GMT

how should i check it?

I created a text object with

=today([timer_mode] )

and it shows today's date

Re: How to truncate date list box at today's date

MK_QSL — Thu, 10 Apr 2014 14:10:00 GMT

Try

Today()

Only..

Load you qvw file by removing all fields except the Date..

Re: How to truncate date list box at today's date

Thu, 10 Apr 2014 14:21:09 GMT

today() only yields 8/4/14

why is that?

Re: Re: How to truncate date list box at today's date

MK_QSL — Thu, 10 Apr 2014 14:47:42 GMT

If your file is on server, restart the server...

Close the qlikview apps and reopen the file...

Re: How to truncate date list box at today's date

Roop — Thu, 10 Apr 2014 14:55:42 GMT

How about scripting your date so that it automatically generates the date up to today:

Let vDateFormat = 'DD-MMM-YYYY';

DateDim:

Load

Date((today() - RowNo()) + 1, '$vDateFormat') as Date

autogenerate (3000);

Re: How to truncate date list box at today's date

Thu, 10 Apr 2014 15:03:04 GMT

hmm... I closed and re-opened Qlikview and got this scary message

Re: How to truncate date list box at today's date

Roop — Thu, 10 Apr 2014 15:05:56 GMT

Always a scary message - sometimes corruption and sometimes you are OK. You just have to hope!

Re: How to truncate date list box at today's date

Thu, 10 Apr 2014 15:14:09 GMT

does it have anything to do with the new list box with expression I added?

Re: How to truncate date list box at today's date

Roop — Thu, 10 Apr 2014 15:25:48 GMT

Unlikely