Pivot table column sorting by other field does not work?

Question & Answer
, , ,
1

image
image1461×486 61.8 KB

I have two calculated fields one is to create weekly intervals, and the other is used to sort those intervals.

image
image978×537 61.5 KB

the first table in the COLUMNS well, it has the Channel Split weekly Intervals calculated field and it is being sorted by the other calculated field (Channel Split weekly Intervals Sort)

I added the sort calculated field to the second table to make it more clear how the column should be sorted.

my question is why the first table, although it is being sorted by Channel Split weekly Intervals Sort, does not have the correct order of columns? the column highlighted in red should be the last one.

2

Hi @Ali_B,
Would you be able to share the calculated fields you’re using for this?

Thank you!

3

Here is the main calculated field:


    ifelse(
        SHIFTDATE >= addDateTime(-364, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-357, 'DD', ${pChannelSplitDate}),
        concat(
            formatDate(addDateTime(-364, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd'),
            ' - ',
            formatDate(addDateTime(-357, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd')
        ),
       
        SHIFTDATE >= addDateTime(-252, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-245, 'DD', ${pChannelSplitDate}),
        concat(
            formatDate(addDateTime(-252, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd'),
            ' - ',
            formatDate(addDateTime(-245, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd')
        ),
       
        SHIFTDATE >= addDateTime(-168, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-161, 'DD', ${pChannelSplitDate}),
        concat(
            formatDate(addDateTime(-168, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd'),
            ' - ',
            formatDate(addDateTime(-161, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd')
        ),
       
        SHIFTDATE >= addDateTime(-84, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-78, 'DD', ${pChannelSplitDate}),
        concat(
            formatDate(addDateTime(-84, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd'),
            ' - ',
            formatDate(addDateTime(-78, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd')
        ),
       
        SHIFTDATE >= addDateTime(-77, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-71, 'DD', ${pChannelSplitDate}),
        concat(
            formatDate(addDateTime(-77, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd'),
            ' - ',
            formatDate(addDateTime(-71, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd')
        ),
       
        SHIFTDATE >= addDateTime(-70, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-64, 'DD', ${pChannelSplitDate}),
        concat(
            formatDate(addDateTime(-70, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd'),
            ' - ',
            formatDate(addDateTime(-64, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd')
        ),
       
        SHIFTDATE >= addDateTime(-63, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-57, 'DD', ${pChannelSplitDate}),
        concat(
            formatDate(addDateTime(-63, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd'),
            ' - ',
            formatDate(addDateTime(-57, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd')
        ),
       
        SHIFTDATE >= addDateTime(-56, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-50, 'DD', ${pChannelSplitDate}),
        concat(
            formatDate(addDateTime(-56, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd'),
            ' - ',
            formatDate(addDateTime(-50, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd')
        ),
       
        SHIFTDATE >= addDateTime(-49, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-43, 'DD', ${pChannelSplitDate}),
        concat(
            formatDate(addDateTime(-49, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd'),
            ' - ',
            formatDate(addDateTime(-43, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd')
        ),
       
        SHIFTDATE >= addDateTime(-42, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-36, 'DD', ${pChannelSplitDate}),
        concat(
            formatDate(addDateTime(-42, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd'),
            ' - ',
            formatDate(addDateTime(-36, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd')
        ),
       
        SHIFTDATE >= addDateTime(-35, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-29, 'DD', ${pChannelSplitDate}),
        concat(
            formatDate(addDateTime(-35, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd'),
            ' - ',
            formatDate(addDateTime(-29, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd')
        ),
       
        SHIFTDATE >= addDateTime(-28, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-22, 'DD', ${pChannelSplitDate}),
        concat(
            formatDate(addDateTime(-28, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd'),
            ' - ',
            formatDate(addDateTime(-22, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd')
        ),
       
        SHIFTDATE >= addDateTime(-21, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-15, 'DD', ${pChannelSplitDate}),
        concat(
            formatDate(addDateTime(-21, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd'),
            ' - ',
            formatDate(addDateTime(-15, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd')
        ),
       
        SHIFTDATE >= addDateTime(-14, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-8, 'DD', ${pChannelSplitDate}),
        concat(
            formatDate(addDateTime(-14, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd'),
            ' - ',
            formatDate(addDateTime(-8, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd')
        ),
       
        SHIFTDATE >= addDateTime(-7, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= ${pChannelSplitDate},
        concat(
            formatDate(addDateTime(-7, 'DD', ${pChannelSplitDate}),'yyyy-MMM-dd'),
            ' - ',
            formatDate(${pChannelSplitDate},'yyyy-MMM-dd')
        ),
        null
    )

and here is the calculated field used for sorting:


    ifelse(
        SHIFTDATE >= addDateTime(-364, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-357, 'DD', ${pChannelSplitDate}),
        1,
        SHIFTDATE >= addDateTime(-252, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-245, 'DD', ${pChannelSplitDate}),
       2,
       
        SHIFTDATE >= addDateTime(-168, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-161, 'DD', ${pChannelSplitDate}),
       3,
       
        SHIFTDATE >= addDateTime(-84, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-78, 'DD', ${pChannelSplitDate}),
        4,
       
        SHIFTDATE >= addDateTime(-77, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-71, 'DD', ${pChannelSplitDate}),
        5,
       
        SHIFTDATE >= addDateTime(-70, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-64, 'DD', ${pChannelSplitDate}),
       6,
       
        SHIFTDATE >= addDateTime(-63, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-57, 'DD', ${pChannelSplitDate}),
       7,
       
        SHIFTDATE >= addDateTime(-56, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-50, 'DD', ${pChannelSplitDate}),
       8,
       
        SHIFTDATE >= addDateTime(-49, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-43, 'DD', ${pChannelSplitDate}),
        9,
       
        SHIFTDATE >= addDateTime(-42, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-36, 'DD', ${pChannelSplitDate}),
        10,
       
        SHIFTDATE >= addDateTime(-35, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-29, 'DD', ${pChannelSplitDate}),
       11,
       
        SHIFTDATE >= addDateTime(-28, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-22, 'DD', ${pChannelSplitDate}),
       12,
       
        SHIFTDATE >= addDateTime(-21, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-15, 'DD', ${pChannelSplitDate}),
       13,
       
        SHIFTDATE >= addDateTime(-14, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= addDateTime(-8, 'DD', ${pChannelSplitDate}),
        14,
       
        SHIFTDATE >= addDateTime(-7, 'DD', ${pChannelSplitDate}) and SHIFTDATE <= ${pChannelSplitDate},
        15,
        16
    )
4

Hi @Ali_B,
This is really interesting, looking at your calculated fields, I don’t see anything that could be leading to this and since the other time windows seem to be working, I wonder if this has something to do with the data breakdown.
One work around you could potentially try; You could add the column with the number values, sort by that field and then ‘hide’ that field from the visual.

Let me know if you have any additional questions or if that work around could work for your case!

1 Like
5

Hi @Ali_B,
It’s been awhile since we last heard from you, following up to see if you had any additional questions?

If we do not hear back within the next 3 business days, I’ll go ahead and close out the topic.

Thank you!

6

Hi @Ali_B,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.

Thank you!