Hello, I am working on a dashboard for our Learning team. They need a widget which reports on group sizes in buckets (1 -20 tickets , 21 -40, etc). I'm using a formula but I must be doing something wrong because the values returned are incorrect.
This is my formula: SUM( [Order ID], if ([Total Ticket Count]<=20, [# of unique Constituent ID], NULL))
Any advice would be much appreciated.
Thank you,Thanos
Hi Thanos, To clarify, you're looking for the number of orders with those ticket counts, right?I would do something like this (with a Row of Production Season, or Perf Year):
SUM ( [Order ID] , IF ( [Total Ticket Count] >= 10, 1 , NULL ) )
This is saying "give me all the orders where the Ticket Count is greater than 10". Technically, it's asking "Put the orders with 10 or more tickets as 1, and orders with 9 or less as NULL. Add up the 1s".
You would then adjust the parameters, by including some AND statements:
SUM ( [Order ID] , IF ( ( [Total Ticket Count] >= 10 ) AND ( [Total Ticket Count] <= 20 ) , 1 , NULL ) )
By adjusting the two (or more) statements (10 or more, and 20 or less), you can set up a whole series of useful formulas!
This is asking "show me the number of orders with 10 or more tickets, and 20 or less tickets". Or "show me the orders with 10-20 tickets".
If you're looking for the # of constituents with such group sizes, you'd adjust your custom formula to SUM the Unique Constituent IDs, rather than Order IDs.
Thank you so much Nathanael! That's done it.