Hi everyone!
I had brought this question to analytic Coffee and I'm still puzzling through and wanted to share here.
I'm trying to create a line chart that compares productions in different years by order weeks prior to performance – however, I want it to look at an running sum proportional to the total sales. This would be for looking at historical data research rather than active sales. Below is a screenshot of how the chart currently looks - because I'm looking at sales pre-pandemic and after reopening, the totals are vastly different, but I want to compare the two lines to where the relative pace to total sales was similar or different. I could hard-code the final totals, but I want to be able to change things out to compare different performances.
Here's a look at the current chart:
Here's what I want to chart to look like - this has the totals hard-coded into the formula, but I want to be able to change the production filters on the chart without having to change to formula every time.
Other potential use cases might be comparing the pace of subscriptions to single ticket sales leading up to a performance, or a fundraising campaign breaking out different donor groups to compare responses after a mailing is sent.
Thanks in advance for any help and ideas!
- Christine
Hello Christine,
It sounds like you need to group the sums by Production. Inside of the SUM(), it allows a group by field and a value field separated by a comma.
SUM ( [Group By Field] , ([Filtered Value1]))
You can also add an IsNull around the value in case it has no data.
SUM ( [Group By Field] , IsNull ( ([Filtered Value1]) ))
You can also build it up to basically do a SUMIF function from Excel.
SUM ( [Group By Field] , IF ( IsNull ( ([Filtered Value1]) ) > 0 , ( [Filtered Value] value if TRUE) , NULL value if FALSE ) )
You can also add in an AND condition to the "sumif":
SUM ( [Group By Field] , IF (IsNull ( ([Filtered Value1]) ) AND IsNull( [Filtered Value2] ) > 0 , IsNull( [Filtered Value] )value if TRUE ) , NULL value if FALSE ) )
You can also use the concept to count things. This formula will add 1 every time the first two conditions are true.
SUM ( [Group By Field] , IF ( IsNull ( ([Filtered Value1]) ) AND ( [Filtered Value2] ) > 0 , 1 value if TRUE ) , NULL value if FALSE ) )
I hope this helps,
Neil
Thanks, Neil!
It took me a little bit to work through the formula, but I got it to work using the group by! I realized I needed to add a MAX around the formula so I could have a consistent value and then I was able to use this as the denominator of my functions to get it to be a percentage.
So my final formula for each line in my chart was:
( RSUM ( ( [Ticket Total Paid and Comp] , [Season Fiscal Year] ) ) ) / ( MAX ( SUM ( ( [Order Weeks Prior to Performance] ) , ( [Ticket Total Paid and Comp], [Season Fiscal Year1] ) ) ) )
Thanks!
Christine
Nice! That is a long formula. Keeping track of the concepts you are using in a formula is half the battle.
Another helpful concept is you can rename the different parts of your formula. This often helps when you revisit it later and try to remember what you did a year ago. The picture below is from Chris Wallingford's Membership Trends dashboard. When I looked at the formula and saw what the filter parts of the formula where renamed to, I thought now that is a great idea. If the expiration and initiation parts below were not renamed, you would only see two "Days in Dates" and be thinking what is the formula doing. Renaming them makes it very clear. Analytics is really just a bag of tricks and you keep adding to it. You just need to have a document(s) that holds all your tips/tricks.
I hear that! The visual renaming of formula parts has been hugely helpful for me. And yes, it's a full-time job just keeping track of formulas, changing formulas, and all of that.
Oh that is so nifty! I had no idea that feature was there - that will be so helpful for keeping track of formula filters.