Question from our customer:
When using the pivot table to pivot month, the month number starts with 1 and then follow by 10 instead of 2, 3, 4,… Why is this the case and how can I fix this?
This is because currently, Holistics pivot table doesn’t recognize number as its field, thus when sorting the data, 10 will comes before 2.
While we’re working on a patch to fix this properly, one workaround is to pad the single-digit month with padding zero so that it becomes
03, … you can do this by modifying your query to extract month number to:
-- postgresql select lpad(month::varchar, 2, '0') as month -- mysql select lpad(cast(month as varchar), 2, '0') as month
And you will get the correct answer:
p/s: FYI, the full query to reproduce the data:
with M as (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)), C as (values ('Books'), ('Laptop'), ('Phones')) select C.column1 as product, lpad(M.column1::varchar, 2, '0') as month, random() * 10000 as sales from M cross join C