Filters & Drill Throughs - Is there another way?

Yellow Belt

Filters & Drill Throughs - Is there another way?

I find myself in this pickle because of the nature of my data. I have employee data that is batched monthly. So each month has all people ever employed at the company(along with info such as hire date, termination date, position, department etc..). I've already done some manipulation in ETL so that when an employee is terminated, the month of their termination is their last entry. 

Because I can have 1, 2, or 3 entries per employee per quarter I couldn't find a way to graph by quarter without my counts being over actual value. So in ETL I created a column to work as a flag for the last entry for each employee for each quarter. Filtering by this flag gives me the exact quarter graph that I want, however I wanted to drill through to show monthly data of that quarter. Because I am filtering for that flag column to get accurate quarterly data I cannot drill down to monthly data because the data is filtered out in the main card. 


I cannot thing of a reasonable work around that would allow me to see both accurate quarterly and monthly data within the same drill path. 


I'm open to any and all suggestions, below are charts of monthly data and then how the filter is used to get the quarterly view. 


Image from GyazoImage from Gyazo

Major Red Belt

Hi @Crisocir 


If you're populating a second column with the last date is this column NULL for records where it's not the last date? If that's the case can you graph a count of this second column and not a count of the original column?

**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**
Black Belt

ok, so if i understand you correctly,

every month you take a snapshot of the employee table.  and then you also have a flag that says 'most recent record' or something like this that gives the current state of the employee.  and you want to navigate from the 'most recent record' to a previous month.


what you could do is have two datasets (use a dataset view, beta feature, talk to your CSM).  


create a dataset view that just has 'most recent records' apply the filter in the view so you don't have to apply the filter in the card.


then as you drill down navigate to a different dataset that has all your data.

Jae Wilson
Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Domopalooza 2021 Call for Presenters: We want to hear how Domo is revolutionizing the way you do business!

Click here to submit your story.