Hello all,
Just a quick one, I'm looking to try to plot a unique data point according to an x-axis or y-axis and then label it. A hypothetical example would be, I'd like to show a timeline of ticket sales with a marker showing when further COVID restrictions were announced, for example: 1 July-- COVID Restrictions announced. If the timeline was something like Perf Date, can anyone suggest an approach to try to achieve this?
Another example might be like "breakeven point" for ticket sales or something along those lines.
Grateful for any advice from any of you experts out there!
Jenna
Hi Jenna,
I typically do this with a dedicated value that turns the axis date into a number and then compares that to a hard-coded number representing the desired annotation date. E.g.
IF ( MAX( [Calendar Year]*10000 + [Calendar Month]*100 + [Calendar Day Of Month] ) = 20210722 , RSUM ( [Total Ticket Count] ) , NULL )
Then rename the value to "1 July-- COVID Restrictions announced". You can replace the RSUM( [Total Ticket Count] ) with any other value, and can change the chart type for just this value to a column, or whatever changes help make the label visible and distinguished from the primary value, without being overly invasive visually.
Chris, that is genuinely brilliant.
I second the motion for declaration of brilliance!
Thirded. I'm good with Y-Axis (like adding a sales target using a bucket), X axis stuff is tricky. I usually export into Photoshop.
Hi Jerry,
If you still want to set a specific Date, and then have it annotate a given Week in Date, this is probably the most reliable approach:
For each date in the given x-axis week, see if that date is the desired annotation date, and if so, return a 1 to the Week. If the max value in any given week then is 1, render the RSUM( [Total Ticket Count] ) annotation value.
IF ( MAX ( [Days in Date] , IF ( MAX( [Calendar Year]*10000 + [Calendar Month]*100 + [Calendar Day Of Month] ) = 20210722 , 1 , NULL ) ) = 1 , RSUM ( [Total Ticket Count] ) , NULL)
That's worked a treat. Thanks Chris!
JH.
Thanks very much all for your help, and will give that a try and come back with questions if I have any (very likely!) haha. Thanks again.
Chris Wallingford As predicted I'm having trouble-- thanks so much for your help:
So I tried the formula you suggested, and just put in a dummy value of 100, I then get this issue (which I've encountered many time before) where it will not display both values at once. So for instance, it toggles one value off whenever the other is activated. Could you explain in general why this toggling off happens? Any advice on this particular example? In this specific instance, I've got Order Date Days in Date as the x-axis category and I've got a simple order count by order date as the other value in the column chart.
Based on the behavior you describe, it sounds like you also have a field in the Break By panel. If that's correct, column, bar, line and area charts only support rendering a single value when there is also a break by field in play.
Hey Chris - trying to use this magic forumla again :-) This time with days in date. I've tried pasting the above formula into analytics but the criteria is going in as plain text, so I need to replicate it. Are 'calendar year', 'calendar month' & 'calendar day of month' performance date criteria?
It's whatever you have on the x-axis. For a a sales curve over order dates those fields would come from the ORDER DATE dimension. For an attendance trend over performance dates those fields would come from the PERFORMANCE DATE dimension.
Ah, thank you Chris.