Any chance you could share a packaged workbook?
Workbook is attached, kinda messy so apologies in advance. I'm mostly using Calculation 1, DP Normal Date, and DP Open Date
I've gotten it to work alright except for the fact that if two accounts have the same open date (Month-Year) it aggregates instead of overlaying on that account's specific line.
Also, I want to use a moving average but when I apply it, the overlay dates are moved off of the account lines.
Any help you could give would be much appreciated!
Adoption Surge v1 TEST.twbx 117.9 KB
1 of 1 people found this helpful
I think there are probably a few different ways to do this, but here's my approach. Change your Calculation1 field to the following (I've also renamed it):
Is Open Date?
IF [DP Normal Date] = [DP Open Date] THEN
Then modify your dual axis so that the second axis also uses Month(Date). Next, change the second axis to a shape mark type and drag Is Open Date? to the shape card.
If you don't have one already, create a custom shape that is 100% transparent. I'll attach mine here in case you're not sure how to do that.
Now set Null to use the transparent shape and Open to use something else.
You'll end up with something like this:
I've attached the packaged workbook.
Wow thank you so much, this is fantastic! Love the approach of using a transparent shape, that's very clever.
Is there any way to achieve this same result when SUM(Number of Records) is converted to using a moving average? When I apply a moving average to the workbook, the Open Date markers are moved off of the line. Is there any way to set an order of operations to calc the moving average first and then apply the Open Date markers?
The problem you're seeing is that the table calculation for moving average isn't accounting for the Is Open Date? dimension. To fix this, right-click on the moving average pill and choose "Edit Table Calculation". Then change it to look like this:
The dots will then appear on the line:
See attached workbook.
Ah nice! I knew I had to use a Compute Using, but I didn't think to compute using both the Date and the Is Open Date. I tried Compute Using -> Is Open Date from the right click dropdown, which doesnt calc along Date as well.
Thank you so much for the help Ken Flerlage you really saved me a lot of trouble. I appreciate it!