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.
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 );
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.