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
- :
- Beast Mode, ETL/Dataflow
- :
- Re: MAX or SUM With Group BY ID

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

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

04-12-2018
03:14 PM

04-12-2018
03:14 PM

I'm looking for a Beast Mode formula to create a MAX calculation __by ID__ in my table.

My resulting column should look like column B below.

I already tried a formula with Distinct and MAX but while the formula was valid, my attempt still failed at producing desired results.

Keep in mind that in my scenario, column A is a calculated field already, don't know if that makes a difference.

Thanks!

ID | column A | column B - max aggregate |

11 | 0 | 1 |

11 | 1 | 1 |

21 | 0 | 1 |

21 | 1 | 1 |

31 | 0 | 0 |

31 | 0 | 0 |

Accepted Solutions

Highlighted

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

04-12-2018
04:49 PM

04-12-2018
04:49 PM

What you want is an analytic function that evaluates the max of the ID window and adds a column with that value.

Domo has done some work on beast mode window functions but they aren't currently available, unfortunately. For now you'd have to bring that into a dataflow where you CAN perform window functions.

Aaron

MajorDomo @ Merit Medical

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

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

MajorDomo @ Merit Medical

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

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

All Replies

Highlighted

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

04-12-2018
04:49 PM

04-12-2018
04:49 PM

What you want is an analytic function that evaluates the max of the ID window and adds a column with that value.

Domo has done some work on beast mode window functions but they aren't currently available, unfortunately. For now you'd have to bring that into a dataflow where you CAN perform window functions.

Aaron

MajorDomo @ Merit Medical

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

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

MajorDomo @ Merit Medical

**Say "Thanks" by clicking the thumbs up 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
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

04-12-2018
04:55 PM

04-12-2018
04:55 PM

Thanks for providing this information.

You are correct, I just got off the phone with DOMO support and they said the same thing

For now I'll have to use the dataflow.

Best.

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

05-28-2018
01:58 PM

05-28-2018
01:58 PM

I was very excited to see beast mode window functions in the #DP18 videos, so hopefully it is now enabled. But if not, the best way I’ve found for doing table calculations is a redshift sql DataFlow. The syntax for window functions is actually very readable and straightforward. Much simpler than trying to get head round the equivalent “table calculations” in ~~Excel ~~Tableau.

In your example it would be:

select *,

max(col) over (partition by id)

from table

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

05-29-2018
12:39 PM

05-29-2018
12:39 PM

thanks for suggesting sharing this work around.

I'll raise it's possibility to a colleague as I'm not familiar with a "redshift sql DataFlow"

Cheers.

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

05-29-2018
01:46 PM

05-29-2018
01:46 PM

No probs. It's a Beta feature, so you will need to ask DomoSupport to turn it on. It appears as a choice when you press "SQL" type connector.

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

12-13-2018
12:29 PM

12-13-2018
12:29 PM

@user02996 wrote:I'm looking for a Beast Mode formula to create a MAX calculation

by IDin my table.My resulting column should look like column B below.

I already tried a formula with Distinct and MAX but while the formula was valid, my attempt still failed at producing desired results.

Keep in mind that in my scenario, column A is a calculated field already, don't know if that makes a difference.

Thanks!

ID column A column B - max aggregate 11 0 1 11 1 1 21 0 1 21 1 1 31 0 0 31 0 0

******************

You can accomplish this in a beastmode. To test do the following:

Create a Domo webform with the ID and Column A information listed above. Then in the analyzer create a table card and add a beastmode for Column B that looks like this:

max(`Column A`) over(partition by `ID`)

Your result should be a table that looks like :

ID Column A Column B

11 | 0 | 1 |

11 | 1 | 1 |

21 | 0 | 1 |

21 | 1 | 1 |

31 | 0 | 0 |

31 | 0 | 0 |

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

12-13-2018
02:07 PM

12-13-2018
02:07 PM

@Wills Are these officially supported functions now? They've been somewhat available in a hush-hush way for a bit, but without support, documentation, etc, I feel adoption isn't what it could be.

Aaron

MajorDomo @ Merit Medical

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

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

MajorDomo @ Merit Medical

**Say "Thanks" by clicking the thumbs up 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
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

12-13-2018
10:30 PM

12-13-2018
10:30 PM

The vertica beastmode functions used to be requested on/off item, meaning perpetual alpha/beta. These functions are now turned on for everyone by default but done so without documentation. So they are more like a secret menu item from in-n-out. This windowing function is not really supported due this lack of documentation and the fact that it may be replaced completly with a magic ETL dataflow "Rank and Windowing" tile as the real long term method for windowing calculations.

So enjoy that is is there but support will always direct you to the magic tile.

Announcements