Hello all,
We are looking to do some further analysis of our existing constituents and their active buying over fiscal years. The question(s) we are trying to answer is along these lines:
How many patrons purchased 1-10 times in the last 10 fiscal yearsHow many patrons purchased 11-20 times in the last 10 fiscal yearsHow many patrons purchased 21-30 times in the last 10 fiscal years
If anyone has a formula for the above that they can pass along, I'd be most appreciative!
Thanks
Mike
Hi Michael,
A pattern like this should do you...
SUM ( [Constituent ID] , IF ( ( [# unique Order ID] , [Season Current Fiscal Year Offset between -10 and 0] ) >= 11 ( [# unique Order ID] , [Season Current Fiscal Year Offset between -10 and 0] ) <= 20 , 1 , NULL ))
Change the 0 to -1 in the FY Offset if you don't want to count the current FY, or change -10 to -9 if you want the 10 years to include this FY.
Hi Chris - I'm working on that now:
SUM ( [Constituent ID] , IF ( ([# of unique Order ID] , [Season Fiscal Current Year Offset] >=1 ([# of unique Order ID] , [Season Fiscal Current Year Offset] <=20 , 1 , NULL ) )
The formula won't close, telling me a syntax error - thoughts on where it is?
Close ")" at the end of both values. E.g.
( [# of unique Order ID] , [Season Fiscal Current Year Offset] )
Hey Chris - Is something else still off as this won't save here. SUM ( [Constituent ID] ,IF ( ( [# of unique Order ID], [Season Fiscal Current Year Offset4]) >=11( [# of unique Order ID], [Season Fiscal Current Year Offset4]) <=20, 1, NULL ))
Would an 'AND' be needed to link the two buckets?
SUM ( [Constituent ID] ,IF (( [# of unique Order ID], [Season Fiscal Current Year Offset4]) >=11
AND
( [# of unique Order ID], [Season Fiscal Current Year Offset4]) <=20, 1, NULL )
)
Hi Nathanael - that did allow that formula to work.
Thanks for the assistance! Now I'm just trying to figure out what would be best for a row.