Visokio website     Downloads     Video tutorials     KnowledgeBase  
Pivot view: Sorting Column Headers in descending date order - Visokio Forums
Pivot view: Sorting Column Headers in descending date order
  • dabay March 7, 2016 6:39AM

    I have a pivot view that has the date columns along the x-axis, is there a way to sort the columns in descending order so the latest date appears as the left most column. Below is the screenshot of the original report where it only orders the dates in ascending order.

    pivot view.JPG 80K
  •     paola March 7, 2016 7:02AM
    Omniscope assumes here you are dealing with a time series and chronological order is default.
    You could force the descending order - duplicate the [Date] field to create a new field (either in the DataManager or Data>Manage fields), selecting the data type Category for the new field, then in :
    Data>Manage fields>Date field options>Value order, colours, shapes
    using the little hand at the end of each row drag the date values to create the desired order. This field can now be used for the Pivot view.
  • dabay March 7, 2016 9:23AM
    Thanks Paola, I created a new field called Run Date and assigned it as category data type see picture:

    But I am not sure about the instructions after this. Since this is a pivot view, the dates can change each day (e.g. new dates get added and old ones get removed) therefore the report needs to dynamically order the dates in descending fashion each time without setting up any manual sort orders. Would this be possible?
    new run date field.JPG 50K
  •     paola March 7, 2016 12:53PM
    In order to force this behaviour dynamically it is best to use the DataManager operation blocks:
    1. Field filter to isolate the [Date] field
    2. Aggregate or De-duplicate the records to end up with a unique list of dates
    3. Sort in descending order
    4. Field organiser to add a formula field = CURRENTROW and give it name e.g. [Desc Date]
    5. Merge/join with the main dataset on the [Date field]

    Now you can load it into Omniscope and create a Pivot view, where on either axis both fields are used in cross tab: [Desc Date] and [Date] . The order field should be the first on the list, so Omniscope will use it to create order.

    Demo file attached.

  • dabay March 8, 2016 4:49AM
    Thanks Paola, I'm wondering if I can achieve the same results using fewer components using the RANK function in a field organiser, but I am confused by the help and examples:

    e.g. would this be the correct syntax to rank the dates from 1 to 10?
    RANK(10, [Date field], "descending")
  •     paola March 8, 2016 9:36AM
    When using Rank function on raw data you will often end up with an irregular sequence, where multiple records share the same value, in which case next rank will skip several values. To avoid this you will need to follow the same steps - isolate and de-duplicate the dates, to end up with a unique list of values, before ranking...
    Alternatively you can use DenseRank function. Following posts will offer help with syntax through demos.

    Syntax to use to create descending order=
    RANK([Date],[Date], false,false)


It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership