How to create a current date column and load the current date for all the values in the sheet

[#1]

Hi Everyone,

I am trying to find the age of a a few products on a list, meaning the products were moved to a particular location and I am trying to find how long they have been there in that location. The spreadsheet that I have attached has the date the products were moved on along with material etc. I want to build a chart that shows the age i.e. the difference between today's date and the date moved for each product. I'm finding it difficult to create a current date column in the Qlikview reloading script. My idea is once I create the current date column with the current date, I can create another column that will find the difference between these two date columns and provide me the age or the time the products have been in the location.

Could you please help me out in creating the current date column. What script do I use and how do I do it. I tried using the Today() function but did not work.

Thanks,

Joe

[tamilarasu · Accepted solution]

Hi Joe,

Try this,

Let vToday = Today();

Data:
LOAD DateMoved, 
     Material,
     [Storage location],
     '$(vToday)' as [Current Date],
     Interval('$(vToday)'- DateMoved, 'dd') as [Age In Days]
FROM
[Finished Goods Inventory - Copy.xlsx]
(ooxml, embedded labels, table is Sheet1);

[#3]

Hi Tamil,

Thank you. It worked. This is great.

My question is what date does Qlikview take? Does it take the date in your computer or does it have its own timer. I changed the date in my computer to tomorrow's date and reloaded the script but the change did not reflect in Qlikview. Hence the doubt.

Thank you very much once again.

[MindaugasBacius]

LOAD DateMoved
    ,today() - DateMoved as Age
    ,Material
    ,[Storage location]
FROM

(ooxml, embedded labels, table is Sheet1);

Result looks like following:

Also, suggest you to sort the data ascending or otherwise in the bar chart.

Please find the attachment.

[#5]

Thanks, This is great. So does the current date change when I change it in my computer.

[tamilarasu]

Hi Joe,

I copied the below explanation from help file.

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

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.

I did a test by changing the system time and it's working fine. I am using Qlikview version 12. You can test the today function in front end. In textbox, try like =Today(), =Today(0).

[#7]

Thanks Tamil. It works now.