Hello all,
Relative beginner here trying to work up a solution for building some basic targets straight into Analytics. We’ve assessed the target/budget field options exported from Tessitura and have determined for the short term we think this is the best way to do it. We're tracking footfall only so not a financial target, just a basic number that we want to put in and track against.
I’ve written a CASE statement to spit out key values which I want displayed on the widgets. This is working really well for the first widget I’ve made but not the second, and I can’t figure out why! The only difference is I’m switching the category parameter to sort by Months in Date instead of Days in date.
First chart is showing daily sales against a simplified daily target. This is OKAY, but we’d rather show a monthly total target an display that against an aggregate of all the monthly sales, only this isn’t working!
Below is an image of the working chart. On this one I’ve also experimented with flexing based on a month and a day of week so we can set a different target monthly for example, for all Mondays in July, or for all Saturdays in August. Targets below are fake so don’t judge me for my dodgy target setting!
This all seems to be working really well with the following formula:
CASE
WHEN [Total Calendar Month] = 7 AND [Total Day Of Week] = 1 THEN 8000
WHEN [Total Calendar Month] = 7 AND [Total Day Of Week] = 2 THEN 8500
WHEN [Total Calendar Month] = 7 AND [Total Day Of Week] = 5 THEN 3000
WHEN [Total Calendar Month] = 7 AND [Total Day Of Week] = 6 THEN 8000
WHEN [Total Calendar Month] = 7 AND [Total Day Of Week] = 7 THEN 7000
WHEN [Total Calendar Month] = 8 THEN 10000
WHEN [Total Calendar Month] = 9 THEN 6600
WHEN [Total Calendar Month] = 10 THEN 6600
ELSE 0
END
As you can see, as soon as we modify the Category to be sorted by Months in Date instead of Days in Date, the formula we have put seems to break—it skips the first logic statements and goes to Else 0.
The formula for this is the same, but even simpler, as follows:
WHEN [Total Calendar Month] = 7 THEN 8156
WHEN [Total Calendar Month] = 8 THEN 9000
WHEN [Total Calendar Month] = 9 THEN 4000
WHEN [Total Calendar Month] = 10 THEN 10000
Does anyone have any advice about why this category swap is breaking it? I'm guessing some sort of mismatch between the way the widget is reading the category data when it is summarised with the way that I am writing the expression in the formula, but I've tried everything I can think of and nothing seems to work. The other data options under Performance Date/ Calendar Month seem to throw a Boolean expression error so [Total Calendar Month] (perf date) seems to be the only option as far as I can tell, or maybe I'm just writing the formula incorrectly?
Also, does anyone have any better ideas for formulas we could use to make this work? The ultimate goal is just to summarise our live sales, but have the target there as a visual indication of what we’re working towards as a simple goal.
Really grateful for the vast expertise of the community on this one if anyone has the time to reply!
Kind regards,
Jenna Whitnall
Andrew Fogel, Owen Craig
Excellent!
Let's start an email conversation. Or conversation over on the analytic Coffee group about who might be interested in participating and when we might want to have a first trial session to gauge interest and the best times.
--Tom