Analysis of the number of purchases per constituent over the last 10 years

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 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?

Thanks

Mike

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.

Thanks

Hi Chris and Nate,

In addition to counting the number of patrons with orders in certain bucket ranges, I also wanted to see the amount of seats constituents had over the past 10 years in buckets.  So I just swapped order id with total seat count.  That seems to also give me an accurate count in buckets as well.

SUM ( [Constituent ID] ,
IF (

( [Total Seat Count] , [Season Fiscal Current Year Offset] ) >=1
AND
( [Total Seat Count] , [Season Fiscal Current Year Offset] ) <=10
, 1
, NULL
)
)

This gives me something like this:

Thanks, both of you for your help.

Please consider Michael, that when grouping by Fiscal Years on rows, the constituents and seats will be constrained by that. So in 2012 and 1-10 Seats, you may be seeing the count of constituents who held between 1-10 seats ... in 2012. You may need to override the grouping using ALL()...

( [Total Seat Count] , [Season Fiscal Current Year Offset] , ALL( [Fiscal Year] ) )

This will still give you ticket holders in Fiscal Year 2012, but the 1-10 seats will be of those with tickets in 2012, how many have had at least 10 seats over the past 10 years.

Thanks, Chris

I adjusted my formula for all the columns,  so it reads now:

SUM ( [Constituent ID] ,
IF (

( [Total Seat Count] , [Season Fiscal Current Year Offset] , All ([Season Fiscal Year] ) ) >=1
AND
( [Total Seat Count] , [Season Fiscal Current Year Offset] ,All ([Season Fiscal Year] ) ) <=10
, 1
, NULL
)
)

Where >=1 and <10 vary for the columns.