@MarkSnodgrass that worked! thank you sir!

@MarkSnodgrass I have tried that some, but its a bit temperamental... I did a join after a group by and it brought over my new columns as "counts" first. Order in which things are done seems to be the issue. Was wondering if there was another method (other than group by) that would avoid those issues.

@MarkSnodgrass Thank you! Hope all is well with you sir.

@jaeW_at_Onyx Thanks!

@MarkSnodgrass that's awesome! Much easier for me to "decode" and use for other things later. Thanks for providing that soluion as well. Hopefully this will help others in the future as well.

@GrantSmith that is incredible! THANK YOU!!!!

@MarkSnodgrass thanks for the reply, unfortunately the dash appears a variable number of times (not just 3). I'll definitely keep that in mind for future. @GrantSmith not sure I understand, but will dig in now to wrap my head around it.

@jaeW_at_Onyx - that's the issue... when I built the card for the deep link it was on my overview page. Thanks for your help! Hope all is well with you sir! @GrantSmith thanks for your response here as well.

Hey @GrantSmith Yes on the pfilter (just one) and this is a card deep link: CONCAT('<a href=''https://XXXXXXXXXXXXX.domo.com/page/-100000/kpis/details/XXXXXXXXX?pfilters=[','{"column":"Order Number","dataSourceId":"XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX","dataType":"string","operand":"IN","values":["',`Order…

@jaeW_at_Onyx & @GrantSmith thank you both for the information.

@GrantSmith I started doing some testing with a window function but it's causing other issues. I agree with what you and @MarkSnodgrass are suggesting here - it's a data side thing I have to do. Thanks for your help. Hope you are doing well.

Sample data attached. I am trying to do with beast mode since the actual application is a "one off" analysis and the dataset we're using it 30M plus rows. Hate to create another aggregated dataset for just one card.

@jaeW_at_Onyx that's it! Thank you sir!

If the resulting dataset isn't massive, you could just pass the completed new SQL flow as an input set in the current ETL and keep the same output step that's powering your cards.

Sum(Case when (your function) = 0 then 0 else (your function) end)

Try this: If the Sales beast mode is SUM(`Sales`), then the % of Sales beast mode would be: SUM(`Sales`) / SUM(SUM(`Sales`)) OVER()

That was it! Thanks @GrantSmith !

@Godiepi @MartinB Ideas?

For grouping in categories I use either a dimensional table that contains the relationships (join on the key), or I create a CASE statement (beastmode or formula in Magic ETL) to create the categories. @user028384 Have you tried these?

Thanks Grant!

@cappie I agree it would be a great feature enhancement to be able to set control limits based on field values in the dataset or beastmodes. You should submit that in the idea exchange - tag me if you do and I will support. I have some other ideas for enhancement as well. The video @jaeW_at_Onyx posted of mine was a…

@user061574 Check out this Knowledge Base article. Focus on the "collapse" and "uncollapse" columns pieces. https://knowledge.domo.com/Prepare/Magic_Transforms/ETL_DataFlows/02ETL_Actions%3A_Edit_Columns

@mynameisyannis to be honest, not sure on how to handle the date format in SQL for your use case. You can probably find on stack overflow - or maybe even a google search. There are also ways to manage date formats in Magic ETL (I use often) where you can specify the "MM-DD-YYYY" vs "DD-MM-YYYY" differences.

@mynameisyannis I typically do these kinds of aging calcs in beast mode. Just seems more simple to build the banding the way you want and update quickly. I would handle the field formatting ahead of the card building layer. Here's an example: Age Banding: CASE When DATEDIFF(CURDATE(),`Order Date`) >=120 then '> 120' When…

Thanks @jaeW_at_Onyx - YOU ROCK!

@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…

@jaeW_at_Onyx - YOU ROCK!

Thanks Jae!

@jaeW_at_Onyx I am attempting to apply what you described in your video here. I built my beast modes and put them in a table card (as you recommended) and they are producing the values I expect. When I attempt to use them in a multi-line card type I am getting an error. Here is a short video I put together:…