• Replies 4 replies
  • Subscribers 962 subscribers
  • Views 81 views
  • Users 0 members are here
Related

Order Weeks Prior to Performance Common Week

Tim Helmer
$organization

Hi,

We want to analyze how our current musical sales are tracking with previous productions. I have a running sum for Paid/Reserved value going along with the weeks prior to performance however the three productions in questions have different lengths of sales time prior to performance . this makes it less friendly to view at glance when comparing weeks. If i filter out the weeks to a common start point, it also filters out any income from the removed weeks. 

is there a way to set the pivot to display beginning at a certain week but keep the running sum of the ticket sales? Even including $0 sales for the performances with shorter sales windows would allow the rows to line up better.  

Thanks! 

  • $organization

    Hi Tim,

    With that filter in place to remove earlier weeks from the comparator productions, a formula could override that filter to get the total sales prior... let's assume your weeks prior minimum is 44, just to pick something, and you've set the filter to only include weeks prior >= -44.

    RSUM(
      IF ( MAX ( [Order Weeks from Performance] ) = -44
        , ( SUM ( [Ticket Count] ) , [Order Weeks from Performance <= -44] )
        , SUM ( [Ticket Count] ) 
      )
    )

    ( SUM ( [Ticket Count] ) , [Order Weeks from Performance <= -44] ) is a Filtered Value, overriding the filter and category grouping on the same field, and instead pulling the total ticket sales from weeks <= -44 into the one week = -44. All weeks after -44 it treats as it normally would.

  • $organization in reply to Chris Wallingford

    Thanks for this! I feel like i'm close but not seeing it yet.  I've set the filter to the first common week of -39, so Order Weeks Prior to Performance >= -39. for the dashboard. 

    for the first show that is longer (selling since -43) i added the following as the value 

     RSUM(
    IF ( MAX ([Total Order Weeks Prior to Performance]) =-39
    , (SUM ([Total Ticket Paid/Reserved Value]),[Order Weeks Prior to Performance <=-39])
    , SUM ([Total Ticket Paid/Reserved Value])
    )
    )

    I don't seem to see any change to Week -39 that indicate it is including the sum from weeks -43 on. 

  • $organization in reply to Tim Helmer

    Hi Tim, 

    For all those fields that read as "[Total something...]", left click on them, select "type >" from the menu, and then select "All Items".

    This is really one of the hardest things for me to explain, and I totally blame Sisense Slight smile. 

    When using an explicit aggregate function like SUM() or MAX(), the value field within the function should be an "All Items" type. When adding a field from the Data Browser, and selecting an aggregate function from there, the aggregate is applied in a hidden way, such that you'll see the aggregate as a description on the field name, like [Total {value field}] or [Max {value field}] rather than as an explicit function like SUM([{value field}]) or MAX([{value field}]). 

    So when we see SUM ( [Total {value field}] ), that's really SUM ( SUM ( [{value field}] ) ), and that MAX ( [Total Order Weeks Prior to Performance] ) is resulting in MAX ( SUM ( [Order Weeks from Performance] ) )...

    Which reads as IF() the [Order Weeks Prior to Performance] from each and every ticket and price layer is added up, does that = -39? It's unlikely. What we should see is either:

    RSUM(
      IF ( MAX ( [Order Weeks from Performance] ) = -39
        , ( SUM ( [Ticket Paid/Reserved Value] ) , [Order Weeks from Performance <= -39] )
        , SUM ( [Ticket Paid/Reserved Value] ) 
      )
    )

    OR

    RSUM(
      IF ( [Max Order Weeks from Performance] = -39
        , ( [Total Ticket Paid/Reserved Value] , [Order Weeks from Performance <= -39] )
        , [Total Ticket Paid/Reserved Value] 
      )
    )

    Just no "SUM ( [Total..." or "MAX ( [Total..."

    Cheers,
    Chris

  • $organization in reply to Chris Wallingford

    Wonderful! thank you for taking the time to explain that so concisely. This is exactly what we were hoping to achieve with this dashboard now.