Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a table in which one of the fields is named date. When I uploaded this to Qlik the dates read:
42736
42767
42795
...
I'm unsure what format this is but I am able to convert this into DD/MM/YYYY using the expression Date(num#(Date,'#')).
This successfully changes the dates and they then read:
01/01/2017
01/02/2017
01/03/2017
But I want to have the dates read just 01/2017, 02/2017...
Is it possible to make the date read in that way? If so, please let me know.
Thanks in advance,
Alison
So I am guessing you are doing something like this... are you?
Income:
CrossTable (MonthYear, Data)
LOAD Income,
[42736],
[42767],
[42795]
FROM .....;
Now you need to do this
Income_Final:
LOAD Income,
Date(MonthStart(Num#(MonthYear)), 'MM/YYYY') as MonthYear
Data
Resident Income;
DROP Table Income;
Try this
Date(MonthStart(DateField), 'MM/YYYY') as MonthYear
Where should this be used, it the data load editor or as an master item expression?
In the script I guess
It should be used in the load editor
Regards
Dates are stored as dual value, the number and a formatted display.
What you are seeing is the number.
in the top of the data load script, the default format is set:
SET DateFormat='DD/MM/YYYY';
change this to:
SET DateFormat='MM/DD/YYYY';
Then you can use:
Date("Date") As FormattedDate
If the date value was a value other than the first of the month, this would force it to be the first of the month. Not sure that was the question, the sample data happened to match that pattern;
In this case, MonthStart would not be necessary to get the formatted output.
You can also do this from within the Data Manager when you edit a table.
Sunny is correct that you should use the MonthStart() function to make the internal value of the dates to be the same. Otherwise you will get multiple values showing "01/2017" in a listbox or chart dimension.
-Rob
Yes, did not read correctly that the OP wanted MM/YYYY