When using a CSV data source, use special prefixes for column names to indicate how data should be interpreted by Flexmonster Pivot. The component has the following prefixes for CSV data:
+– the field is a dimension.
-– the field is a value.
m+– the field is a month.
w+– the field is a day of the week.
d+– the field is a date. Such fields will be split into 3 different fields: Year, Month, and Day. Date formats that are supported by Flexmonster Pivot are described below.
D+– the field is a date. You will see these dates as a hierarchy: Year > Month > Day.
D4+– the field is a date. You will see these dates as a hierarchy: Year > Quarter > Month > Day.
ds+– the field is a date. Such fields will be formatted using a date pattern (default is
t+– the field is a time (measure). Such fields will be formatted using
dt+– the field is a date (measure). Such fields will be formatted using
id+– the field is an id of the fact.
Here is the minimal CSV data that will treat
Year as a dimension, rather than a numeric measure:
Country, +Year, Sales US, 2010, 200 UK, 2010, 100
To make date column be interpreted as a date, use prefixes
D4+ for CSV columns. Additionally, data from these columns should have a special date format to be understood properly. The pivot table component supports the ISO 8601 date format, for example:
"2016-03-20" (just date) or
"2016-03-20T14:48:00" (date and time). Other formats aren’t officially supported and may have unexpected results.
Here is an example of CSV data with date columns –
Size, Discount, d+Date1, D+Date2 214 oz, 14, 2009-11-01, 2009-11-09 214 oz, 12, 2010-12-09, 2009-12-09 212 oz, 36, 2009-09-01, 2009-12-01 212 oz, 27, 2009-09-01, 2010-12-02 212 oz, 18, 2010-11-09, 2009-12-11 212 oz, 16, 2009-09-01, 2009-12-20
The pivot table based on this CSV will look as follows:
As you can see, the
Date1 column with prefix
d+ is split into three separate fields —
Day. In the Field List, the
Date1 column will look as follows:
Date2 column with
D+ prefix is interpreted as a hierarchy that can be drilled down to months and days.