• Replies 7 replies
  • Subscribers 963 subscribers
  • Views 133 views
  • Users 0 members are here
Related

Running Sum Overcounting

Joan Shortt-Smith
$organization

Is there a way to stop a running sum from double counting a value that pulls into two separate rows? So only count it the first time it shows up?

For rows I’ve got Order Weeks Since Onsale and values I’ve got running sum (by category) for the number of unique Performance IDs.

It’s for our education bookings where our targets are done by number of performances. Usually it’s one order/constituent per performance but sometimes multiple schools share a performance. If they are booked in separate weeks then my totals are off/overcounted.

Parents
  • $organization

    Hi Joan,

    I think we can do something in a formula to overcome the double-counting of unique members across categories by flagging the first instance of a Performance ID within the widget, and only counting those first occurrences.

    RSUM(
      SUM([Performance ID],
        IF([Min Order Weeks from On-sale] = ([Min Order Weeks from On-sale] , ALL([Order Weeks from On-sale]))
        ,1 , 0
        )
      )
    )

    I'd read this as, if the order weeks from onsale for this row is the minimum or first order weeks from onsale for this performance ID, then return a 1 to the sum, otherwise return a 0, and then rsum that result.

  • $organization in reply to Chris Wallingford

    Hi Chris

    Thanks very much for that. If I run this formula to get the weekly totals it gives the totals without the double counting.

    If I then do the rsum of that I get Error Querying ElastiCube – an unknown error has occurred.

    Any tips?

Reply Children