jimsteph Contributor

I apologize: I didn't see that @Parker_Lake submitted the same idea a few weeks ago. My search skills seem to be lacking, and I don't think I can delete my original post.

That's what I feared. So it looks like I'll have to use formulas to test each hour against the start and stop time, then unpivot to get a row per employee/hour. Thanks for your help.

I would take it a step further and make it so that if you make a change to a tile after a preview you can tell it to recalculate the preview for that tile without having to recalculate all of the preceding tiles. This is already available in the SQL ETLs, and would be a huge time saver.

Ten thousand thumbs up for colorizing the column headers to see where the column came from!

That's a huge help, and solved the immediate problem. Thanks! That said, I forgot to mention that I'd also like to have a field called StatusChangeDate that, for all of the groupings, would have the earliest date of the grouping: Date Status Dense Ranking Status Change Date 1/1/2023 Active 1 1/1/2023 2/1/2023 Active 1…

Thank you for your response. I will either write a recursive ETL or see if the MySQL engine supports upsert (INSERT INTO with ON DUPLICATE KEY UPDATE) just so I can have a working dataset. I won't be able to use partitioning, however, as I'd want to partition on EmployeeID, and there are way more than the max number of…

If I'm understanding, I'm not sure how this would work: my users might set the dashboard's date filter to month to date, or to previous month, or to previous year, or any of a hundred other ranges, and I can't account for each of those in the flow itself. Is there no way in a beast mode to get the max value across all rows…

@GrantSmith I followed your suggestion and converted to a table, used the ticket date for the first column and then put columns for A, B, C, (A+B+C), and the full formula, set all of those to SUM, and the calculations all worked. I was even able to get the summary number to be correct over the selected date range. Here's a…

This is correct. A, B, and C are derived with logic, but are still in the end flags. Based on your dataset, I would expect to get 7/(7+4-5) = 7/6 = 116.67% In the actual dataset there are 7.722 million records for the month to date. The sum of flag A for this period is 53,952, the sum of flag B is 547,753, and the sum of…

Thank you both. @colemenwilson , I think yours was about the same, but I was able to implement @Jones01's immediately.

I had to think on this for a bit, but it does make sense: anything that would have needed recursion can be recreated by breaking the ETL into two parts and generating an intermediate dataset. Time to get to work … Thanks!

You may want to wrap your attempted conversion in the IFERROR() function. You'd do something like IFERROR(DATE(`mydate`), NULL). If the first argument in the IFERROR() function is successful — in this case DATE(`mydate`) — is successful you get the results of the first argument. If it doesn't, the IFERROR() function…

Good catch! It appeared out of the blue this morning, but I was working on an ETL that is triggered by other datasets finishing, so I didn't notice there was no option for manual trigger. I think you just saved me from pulling more hair out.

Well that's annoying, @Jones01. I'll go ahead and put in a ticket and hope you're wrong, because doubling the number of instantiated rows could get expensive for everyone. Thanks both of you for the response.

I'm a novice with both, but I'm willing to learn, and I've got people here who could probably take it over if I get in over my head.

That is buried, and I would never have found it. Thanks!