Group Aggregates Only

TDVol · 10-26-2021 03:47 PM

I am building a Dashboard based on a table that has columns for Sales (Product A), Sales (Product B) and Sales (Product C). These sales are recorded in a datasheet that has a column for Date and Rep. Basically, I want to be able to subtotal those Sales Columns by Rep. However, my slice includes the column for Date as I want to have a dynamic filter for date ranges.

I have been able to accomplish most of this, however, the dashboard will show a blank row for each date (or occurrence) between the Group Aggregate rows. The totals work. For example if there were 3 days that had sales for Product A, the total is correct. However, AFTER that total, there are 3 spaced rows. No matter what I do, I cannot seem to just get the Aggregate total rows ONLY.

And, if I change it to a chart, it creates multiple bars for each date. Is there a way to hide the occurrence of the detail rows and just show the total ONLY?

Marc_Dillon

I think your problem descriptions needs the assistance of a screenshot or two to show what you are referring to.

Also, how is your Slice defined?

TDVol

The main table has an actions table that includes columns for Prospect, Date, Rep, Action, and Premium Sold. In 1 instance, I am trying to summarize the Premium Sold by Rep. The slice has the columns for Date, Rep, and Premium. So let’s say a given rep has sales on 4 different occasions in the chosen Date Range. There will be a line with the total for that range followed by a line for each individual entry. I only want the total line.

Marc_Dillon

If you add a second Group-By column to your table View that you’re showing in your screenshot, it will activate the “drill-down” Group-By navigation, where the actual records will be collapsed and you’ll just see the group headers and their aggregate value. If you don’t want to actually group by any other column, just add _RowNumber as the second Group-By column. See this article for more on this:

This very simple and easy change may or may not be sufficient for your needs, but it’s a great place to start.

TDVol

Thank you very much. At first pass, this seems to solve the issue. I guess you just need to make sure that your targeted summary level is not the final level of the data you are viewing.

TDVol

Marc, I will follow up to see if there is any such option with a chart view? It seems chart options allow group aggregates, but not necessarily grouping. So, while the 2nd level grouping works on a simple list style view for a dashboard, I cannot find a nice visual way to express that.

Marc_Dillon

For a chart you’ll have to set up something more complicated. I’d advise creating a separate Table to hold the calculated aggregate values, and then charting off of that new Table.