Sort a Pivot table

AttuAk
AttuAk Member
in Charting

Hello All, I am trying to create the beast mode for sorting the Pivot table, please see the explanation below. I have a pivot table where I am trying to have sorted at the Channel attribute level by sorting on the combination of Return_V + Reg. I have created a beast mode to do this but it is currently sorting at the lower level of Offer_N. My table looks like this:

Collapsed Form of Pivot Table:

Channels             Offer_N           Return_V              Reg          Offer_C

+ Search Total             1800                850                550

+ Direct Total                                         2000                950                450


Expanded Form of Pivot Table:

Channels              Offer_N            Return_V              Reg        Offer_C

- Search         EN Wireless E-book           800               500                  250

                        EN Healthcare                  1000               350                  300

- Direct           EN Wireless E-book           900               600                   250

                       EN Healthcare                    1100              350                   200

 

The rows in the collapsed form of table should get collapsed based on Sum of Return_V and Reg. For Example: For Channel = Direct, the Sum of Return_V and Reg is 2950 and for Channel=Search the Sum of Return_V and Reg is 2650. So, the sorted table should show Channel=Direct row on top followed by Channel=Search row, as shown below:

Sorted Collapsed Form of Pivot Table:

Channels                Offer_N         Return_V                Reg           Offer_C

+ Direct Total                                      2000                 950                  450

+ Search Total                                         1800                 850                  550


I created the following Beast Mode and put it in the Sorting section:

Return Visits & Registrations = SUM((CASE WHEN `Engagement Point

Metric` = 'RETURN_V' THEN `Raw count` ELSE 0 END))

+

SUM((CASE WHEN `Engagement Point Metric` = 'REG' THEN `Raw count` ELSE 0 END))

This beast mode is set to Sort in descending order based on the SUM of Return_V and Reg.

This beast mode works by sorting at the Offer_N level since that is the lowest level in the table. However, I need it to sort at the Channel level and sort when the pivot table is collapsed.

Please help on this issue and share if there is any probable solution to allow for sorting at the Channel level rather than the Offer_N level. Thank you.


Here is an image of the actual data and table from Domo:

Capture.PNG


Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    You will need to have Window Functions in Beast Modes enabled in your instance if it isn't already. You can ask your CSM to enable it. You can then create a beast mode like this:

    SUM(SUM(`yourfieldtosum`)) OVER(PARTITION BY `channel`)

    Obviously, replace yourfieldtosum with your two fields you are adding together. Once you have this created, drop it in the sorting properties and sort by descending and it should work.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • AttuAk
    AttuAk Member

    Thank you Mark. I will check with our CSM.

Categories