Date formatting
Hi, I’m working with a csv with date in format dd/MM/yyyy (ie 31/12/2017 for 31st december 2017).
My problem is that the pivot orders my date as
01/05/2018 (1st of may)
01/06/2018 (1st of june)
02/05/2018 (2nd of may)
02/06/2018 (2nd of june)
Is it possibile to fix the sorting?
2 answers
Hello,
Thank you for the question.
Our pivot supports date sorting but seems it doesn’t handle the column type properly with your CSV.
Generally, we recommend using ISO 8601 date format (other formats may be used as well, but results can be unexpected). For example, "2016-03-20" (just date) or "2016-03-20T14:48:00" (date and time).
Also, you can use special flags in CSV header to specify column types explicitly:
d+column is a date. Such column will be split into 3 different columns: Year, Month, Day.D+column is a date. You will see such date as a hierarchy: Year > Month > Day.D4+column is a date. You will see such date as a hierarchy: Year > Quarter > Month > Day.ds+column is a date. Such field will be formatted using date pattern (default isdd/MM/yyyy)
For example:
Size, Discount, d+Date1, D+Date2 214 oz, 14, 2009-11-01, 2009-11-09 214 oz, 12, 2010-12-09, 2009-12-09
Please let us know if it helps.
Regards,
WDR Team
ds+column is a date. Such field will be formatted using date pattern (default isdd/MM/yyyy)
That seems the right choice, at least in theory. But dates are still formatted switching dd and MM. It’s so weird.
I guess I have to parse my csv and change date formats..