topic Re: Seeking Dashboard Aggregate Formula to only show Grouping Dimension with multiple TransNo in Dashboards & Analytics Discussions

Seeking Dashboard Aggregate Formula to only show Grouping Dimension with multiple TransNo

schesher — Tue, 19 Sep 2023 16:38:02 GMT

Thank you for taking the time to look at this - I appreciate your help.

My goal is to reduce an Aggregate Table dashboard to only show records for the primary Grouping Dimension that result in multiple transaction numbers. My Primary Grouping Dimension is a concat formula that combines 5 different measures into one line of text. This has worked really well to get the data to pull, however I am not sure what formula to use to only show concat grouping results that have greater than 1 TransNo. Does anyone have advice on what formula to use here? It would reduce three days worth of data from 11,563 lines that have to be manually edited to only 48 lines that complete the report.

Re: Seeking Dashboard Aggregate Formula to only show Grouping Dimension with multiple TransNo

schesher — Thu, 28 Sep 2023 13:26:21 GMT

to rethink - is there a way to filter down to only show records with more than one result?

Re: Seeking Dashboard Aggregate Formula to only show Grouping Dimension with multiple TransNo

JoeM — Fri, 29 Sep 2023 15:29:13 GMT

Hi @schesher - Not sure where you are looking to settle the logic, but the simplest path might be to write up a simple MV like this:

SELECT * FROM "Aggregate.Input" WHERE "Aggregate.Input.PT_SKU_Location" IN ( SELECT "Aggregate.Input.PT_SKU_Location" FROM "Aggregate.Input" GROUP BY "Aggregate.Input.PT_SKU_Location" HAVING COUNT(*) > 1 );

Re: Seeking Dashboard Aggregate Formula to only show Grouping Dimension with multiple TransNo

JoeM — Mon, 02 Oct 2023 15:58:08 GMT

@schesher - Did this work for you?

Re: Seeking Dashboard Aggregate Formula to only show Grouping Dimension with multiple TransNo

ZachBreimayer — Tue, 10 Oct 2023 18:45:24 GMT

Another approach would be to use an aggregate filter. However I would recomend not having that grouping calculation for the combined key as a formula at the viz layer, maybe back that into a business view. The aggregate filter is essentially a having clause if your familiar with sql. Heres a screenshot of that below.