Sorting dates in Pivot Tables

tasosventouris
tasosventouris Registered Posts: 15 ✭✭✭

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.

Tagged:

Answers

  • Sarina
    Sarina Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 317 Dataiker

    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
    tasosventouris Registered Posts: 15 ✭✭✭

    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
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,981 Neuron

    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
    tasosventouris Registered Posts: 15 ✭✭✭

    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
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,981 Neuron

    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
    annechristo Registered Posts: 1 ✭✭

    @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!

Setup Info
    Tags
      Help me…