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.
Can you use Multipass Aggregation to group by Order_no &/or constituent. I attempted a double aggregation and had it simplified by Dr Wallingford here
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.
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?
Thanks Heath - it does look like multi-pass aggregation is exactly what i need!
Nothing clearly jumps out at me. I tried reproducing the issue on a v15 instance and the widgets rendered as expected. Try importing this dashboard, and if it works for you, try copying and pasting the formula from the imported dashboard to your widget.
/cfs-file/__key/communityserver-discussions-components-files/42/OrderWeeksSinceOnSalerunninguniquePerfID.dash
I think I've seen this quirk where in some cases it needs another set of brackets to the thing you are rsumming to get over the error. maybe?
Thanks Both - i'll keep playing.