Grouped bar chart with filtering top x results over a dynamic date range

RichRaz
RichRaz ⚪️
edited January 4 in Charting

Hello and thank you for taking the time to look at my question. I am fairly new to domo so I may be getting hung up on simple concepts and I appreciate your patience. To preserve anonymity I am using mock data for my example and questions.

I have a dataset derived from a bug tracking system with fields: issue_id, created_date, tag. 

Example:

issue_id,created_date,tag

1,2022-03-22 19:02:03,d

2,2022-03-31 01:15:02,d

3,2022-03-22 10:23:03,a

4,2022-04-28 02:55:29,b

5,2022-03-22 02:06:33,d 


I created a card with a grouped bar chart that uses `created_date as the category (x-axis), count of `tag as the value (y-axis), and `tag as the series which was graphed by year for the past 3 years. However, In my actual dataset, I have close to 50 unique values for `tag so I then limited by row to display the top ~5 results of my series for each year.


Naturally, where things fall apart is when I want to change the “date range” or “graph by” due to limiting rows as a filter. For example, if I wanted to display the last 3 years by quarter, I would need to create another card, figure out how many rows to limit by, and then explain to any stakeholder who wants to change the date range or “graph by” that I will create a new card for them.


After some reading on the dojo and watching youtube videos (thanks Jae!) I attempted to approach this filtering problem using rank and window as a beast mode (https://dojo.domo.com/discussion/52521/show-top-10-in-chart-rank-in-beast-mode). My goal has been to partition by date(created_date), then for each day rank by count of tags for that day, then I would filter based on rank, however, I have been unsuccessful assigning rank, and instead, I seem to just split my data into rows.

The beast mode that I have been attempting is:

rank() over (partition by date(created_date) order by count(tag) desc)


Example of what I am trying to achieve before filtering using the 5 example rows above:

created_date,count of tag, tag,tag_rank

2022-03-22, 2,d,1

2022-03-22,1,a,2

2022-03-31,1,d,1

2022-04-28,1,b,1


I think I am struggling to wrap my head around something fundamental since I cannot seem to get an aggregate count(tag) if using order by. Additionally, I wanted to understand if I am able to get rank displaying correctly, would I run into issues when setting my “graph by” value to something other than how my partition by date value is set (e.g. weeks vs days) and is there a way to work around that? Thank you so much for taking the time to read through this.


Additional mock data here: https://pastebin.com/PNgAz5ZF