Sorting dates in Pivot Tables

tasosventouris

Hey everyone!

I created one Pivot Table as an Insight to include it in my Dashboard. The Dataset has a date column. It is parsed correctly and the Insight has recognised the field as date. In the dataset, the table is ordered by the date field Descending. However, when I create the Insight with the Pivot Table, the only way I can sort it is Ascending. Actually there is no option at all and it changes it to Ascending by default. Is there any way to change that? I prefer to have the latest date on top.

Sarina

Hi @tasosventouris
,

Can you try creating a Sort recipe to sort the data in descending order and then create your chart on the output dataset of the sort recipe? If you still don't see the data sorted as expected, can you attach a couple screenshots showing the mis-sorting?

Thanks,
Sarina

tasosventouris

I create the Pivot Table inside the Insight, so I think it has nothing to do with the Sort Recipe. However, I tried and it still the same. Let me provide some screenshots

The flow where you can see that it's a simple one. Initially I had just the first recipe (custom one which creates the dataset). Second screenshot is from the dataset that has the latest date on top. Sorted Descending. Third screenshot, when I create the Pivot Table which is sorted Ascending by default. I cannot upload a 4th screenshot, but in the Rows input, on the options you get for sorting which let's you sort the pivot table only by values and not by Dates

Turribeach

Hi, your "date" is not a date data type but a string. First I suggest you familiarise with Dataiku storage types and meanings reading this documentation section:

https://doc.dataiku.com/dss/latest/schemas/definitions.html

What you should really care about is the storage type (which is at the top) not the meaning (which is a bottom in blue). So create a Prepare recipe and convert your string to a proper Date data type before trying to sort it. You can easily do this by clicking on the column heading down arrow, selecting Parse Date and selecting the correct date/time format of your column. Once you do that your column should show as date/date (storage type/meaning) and will sort properly.

tasosventouris

I see. Just saw it since I had it in schema as Date. As I can test, whenever I use `write_with_schema` in the python code, the datatype is always string. Even if I explicitly assigned it as Date in the dataset.

Turribeach

The schema is decided explicitely by your code or implicitely by Pandas. You can use write_from_dataframe() if you prefer:

https://doc.dataiku.com/dss/latest/code_recipes/python.html#writing-the-output-schema

But then you will have to manage schema changes manually and propagate them manually in the flow.

annechristo

@Dataiku I have the same issue. I have a pivot table with date on rows and would like to sort it in descending order. I have the data in snowflake datatset and hence when trying to add a sort recipe it gives the warning that the sort is useless because the dataset doesn't preserve the order. Can you please add the functionality to sort pivot tables by rows and also charts by both axes? Thank you!