• Replies 5 replies
  • Subscribers 701 subscribers
  • Views 242 views
  • Users 0 members are here
Options
Related

CASE statement in a Pivot table widget to a SUM

Anastasia Ryan
$organization

Hi friends,

This may need to end up as a support/consulting ticket but I thought i'd put it to the great minds of analytics coffee first to see if anyone can assist me! For some background, I am reviewing the sales dashboard that I created for our 2022 festival and making adjustments and improvements ahead of 2023.

I have managed to use a CASE statement to calculate the box office share for each production and enable display of all of them in the same pivot, which i'm very pleased with as we have a mixture of 50-100% box office shares, imported orders and various levels of fees built in as well. This seems to be working as expected so far, but I am having trouble then taking that statement and turning it into a SUM function so that I can great an indicator widget and also use the formula to plot daily sales on a line graph.

I've tried a few different things and nothing seems to be giving me the same figure as the sum of my pivot table column. The CASE statement i'm using in my pivot is below:

CASE
WHEN ([# of unique Production Season ID] ,[Production Season ID]) = 1
THEN
(sum((([Total Ticket Paid Amount],[Price Layer Category],[Mode of Sale Category])))

+

(sum((([Total Ticket Paid Amount],[Price Layer Category],[Mode of Sale Category1]))
-
(CASE
WHEN ([# of unique Production Season ID],[Production Season ID1]) = 1 THEN (([Total Ticket Count],[Production Season],[Mode of Sale Category1])*2.5)
END))))

WHEN ([# of unique Production Season ID] ,[Production Season ID2]) = 1
THEN
((sum((([Total Ticket Paid Amount],[Price Layer Category],[Mode of Sale Category])))

+

(SUM((([Total Ticket Paid Amount],[Price Layer Category],[Mode of Sale Category1]))
-
(CASE
WHEN ([# of unique Production Season ID] ,[Production Season ID3]) = 1 THEN (([Total Ticket Count],[Production Season1],[Mode of Sale Category1])*5)
END)))) *0.5)
ELSE NULL
END

If anyone can share any expertise here I'd be very grateful!

Thanks,

Anastasia

Parents
  • $organization

    Hi Anastasia,

    So you want to take the values resulting from each of the rows of your pivot table, which is split out by production season to allow for these per/production season values, and see them rolled up in an indicator. Generally I would approach this by taking the conditional WHENs and into the THEN values as filters, and ultimately remove the cast statements.

    Alternatively, if the logic really requires looping through the different productions, you might be able to take that formula and wrap it in a multi-pass aggregation...

    SUM ( [Production Season] ,
      {your formula}
    )

    Hope that helps,
    Chris

  • $organization in reply to Chris Wallingford

    Hi Chris,

    Yeah that's exactly what i'm trying to achieve! 

    Your suggested approach sounds like the road I was going down, I'm just having trouble getting the numbers to match up at the moment as they are always coming out a few thousand dollars out! I'll take it apart and rebuild step-by-step to see if I can work out where the snag is happening.

    Thanks for the push in the right direction, i'll keep working away.

    Anastasia

Reply
  • $organization in reply to Chris Wallingford

    Hi Chris,

    Yeah that's exactly what i'm trying to achieve! 

    Your suggested approach sounds like the road I was going down, I'm just having trouble getting the numbers to match up at the moment as they are always coming out a few thousand dollars out! I'll take it apart and rebuild step-by-step to see if I can work out where the snag is happening.

    Thanks for the push in the right direction, i'll keep working away.

    Anastasia

Children
  • $organization in reply to Anastasia Ryan

    Just to be clear - your MOS filter is for something like sales that you've excluding from your GBO Split (?) and you have multiple per ticket fees built in as Price Layers with distinct categories.

    And you Pivot contains buckets that match your individual case logic

    And the Production Season ID/1/2/3 filters don't contain overlaps

    There are a few moving parts - if you want to jump on a zoom let me know.  We could break down some formulas in Analytic Coffee next week.

    Slight smile

  • $organization in reply to Heath Wilder

    Sooo many moving parts haha. I am about 90% confident i managed to crack this and ended up with the below:

    (sum((([Total Ticket Paid Amount],[Price Layer Category],[Mode of Sale Category],[Production Season ID])))

    +

    (sum((([Total Ticket Paid Amount],[Price Layer Category],[Mode of Sale Category1],[Production Season ID]))
    -
    (([Total Ticket Count],[Production Season],[Mode of Sale Category1])*2.5)
    )))

    +

    ((sum((([Total Ticket Paid Amount],[Price Layer Category],[Mode of Sale Category],[Production Season ID1])))

    +

    (sum((([Total Ticket Paid Amount],[Price Layer Category],[Mode of Sale Category1],[Production Season ID1]))
    -
    (([Total Ticket Count],[Production Season1],[Mode of Sale Category1])*5)
    ))) *0.5)

    Might take you up on the Zoom offer regardless Heath as I'd love another persons eye over things to see if I'm coming at it all from the most sensible direction or not.

  • $organization in reply to Anastasia Ryan

    Hey Anastasia,

    I'm going to dig into this (I promise).  First day on the new job and we don't have analytics here (yet) but we can totally make it work. 
    What I'd love to do is block out the parts of your formula and put descriptions of the business requirements of each of the filters.  It'll turn it into a story narrative that we can play with.

    Cheers,
    Heath