HELP CENTER

HELP CENTER

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Dojo
- :
- Using Domo
- :
- Card Building
- :
- Limiting Pivot Values

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

04-09-2020
06:38

04-09-2020
06:38

I have a pivot table that I am creating with rows broken down into 2 dimensions and values as the count of a 3rd dimension. I would like the output only to display the top 3 Dimension B's for each Dimension A. Below is a screenshot of my current table (intentionally blurred). I would like to display only the highlighted parts of the table. Any ideas how I might go about this?

Thanks!

Accepted Solutions

Highlighted

Jae Wilson

Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.

**Please mark the post that solves your problem by clicking on "Accept as Solution"

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

04-14-2020
12:52

04-14-2020
12:52

@Cartergan wrote:

You can try limiting the data rows and see if this achieves the result you are looking for. In the analyzer view near the bottom right of the screen you can set how many rows you'd like to show, and then you'd need to sort your results how you'd like them to appear.

I would be unsurprised if this method yielded unexpected results, so test carefully. I do not believe that LIMIT N rows would distribute across all the dimension members (combinations of Cat1 > Cat2). More likely, if it's anything like SQL, i suspect it would just take the first N rows without respect to distribution. an explicit ORDER BY clause may appear to help, but i would proceed with caution.

Given my assumption is true, you could try to use logic to force the visualization you want.

"for each cat1 i want to keep the 3 highest ranked rows."

you could try to calculate RANK() and THEN assign a value based on RANK (using a CASE) and set a LIMIT based on a predefined number of CAT1 rows * 3 rank ( ex. LIMIT = 4 *3)

FIRST calculate RANK

rank() over (partition by cat1 order by sum(amount) desc)

THEN assign amount_value based on CASE

CASE WHEN rank() over (partition by cat1 order by sum(amount) desc) > 4 then -1000 else sum(amount) end

I didn't test this pseudo code at all... but the idea is, if the row_rank is greater than 4 then you want to exclude it, so you assign it an extreme value. If you're limiting rows, then all extreme values should hopefully be excluded. I chose row_rank = 4 in the unlikely event of ties, but that messes with your LIMIT.

To use windowed functions in beast modes email your CSM (customer success manager) and ask them to enable 'windowed functions in beast modes'.

Additionally, cc @MarkSnodgrass , @DataMaven , @GrantSmith @swagner , you can ask your CSM to enable 'filter on aggregates' which would allow you to filter on the HAVING clause of a SQL statement in your cards. I personally recommend this with EXTREME caveats insofar as, AFAIK this functionality was rapidly implemented and not thoroughly integrated into card builder. so... use at your own risk and test it a lot.

Jae Wilson

Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.

**Please mark the post that solves your problem by clicking on "Accept as Solution"

All Replies

Highlighted
##

You can try limiting the data rows and see if this achieves the result you are looking for. In the analyzer view near the bottom right of the screen you can set how many rows you'd like to show, and then you'd need to sort your results how you'd like them to appear.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

04-13-2020
04:40

04-13-2020
04:40

Highlighted

Jae Wilson

Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.

**Please mark the post that solves your problem by clicking on "Accept as Solution"

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

04-14-2020
12:52

04-14-2020
12:52

@Cartergan wrote:

I would be unsurprised if this method yielded unexpected results, so test carefully. I do not believe that LIMIT N rows would distribute across all the dimension members (combinations of Cat1 > Cat2). More likely, if it's anything like SQL, i suspect it would just take the first N rows without respect to distribution. an explicit ORDER BY clause may appear to help, but i would proceed with caution.

Given my assumption is true, you could try to use logic to force the visualization you want.

"for each cat1 i want to keep the 3 highest ranked rows."

you could try to calculate RANK() and THEN assign a value based on RANK (using a CASE) and set a LIMIT based on a predefined number of CAT1 rows * 3 rank ( ex. LIMIT = 4 *3)

FIRST calculate RANK

rank() over (partition by cat1 order by sum(amount) desc)

THEN assign amount_value based on CASE

CASE WHEN rank() over (partition by cat1 order by sum(amount) desc) > 4 then -1000 else sum(amount) end

I didn't test this pseudo code at all... but the idea is, if the row_rank is greater than 4 then you want to exclude it, so you assign it an extreme value. If you're limiting rows, then all extreme values should hopefully be excluded. I chose row_rank = 4 in the unlikely event of ties, but that messes with your LIMIT.

To use windowed functions in beast modes email your CSM (customer success manager) and ask them to enable 'windowed functions in beast modes'.

Additionally, cc @MarkSnodgrass , @DataMaven , @GrantSmith @swagner , you can ask your CSM to enable 'filter on aggregates' which would allow you to filter on the HAVING clause of a SQL statement in your cards. I personally recommend this with EXTREME caveats insofar as, AFAIK this functionality was rapidly implemented and not thoroughly integrated into card builder. so... use at your own risk and test it a lot.

Jae Wilson

Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.

**Please mark the post that solves your problem by clicking on "Accept as Solution"

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

04-14-2020
06:05

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

04-20-2020
08:57

04-20-2020
08:57

@jaeW_at_Onyx wondering if I can ask for an example of the basic syntax for including the HAVING clause.

Example for me would be filtering on orders that total more than $100,000. In my data orders can consist of 1 or many line items. I would SUM the 'sales' dollars for each order, and then include those HAVING SUM('sales') >100000.

Let me know.

Highlighted
##

Jae Wilson

Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.

**Please mark the post that solves your problem by clicking on "Accept as Solution"

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

04-20-2020
11:04

04-20-2020
11:04

@swagner here you go!

Jae Wilson

Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.

**Please mark the post that solves your problem by clicking on "Accept as Solution"

How to create a Top N by Category report using #Domo and #Window functions in #BeastModes ##### CONSULTING SERVICES #### I have left Domo to start my own con...

Announcements

Click here to submit your story.