Repeat Cycle

Hi Sarah, 

I found this (+) Average time between visits - Reporting & Analytics - Forums - Tessitura Network

But it doesn't focus on only the last two visits for each constituent. There isn't something natively in Analytics that will flag up those last two visits in a way that will allow us to then compare the time between them and aggregate those results across all constituents. We can get a widget to tell us what those last two visit dates were, but then we're stuck...

To get what you're after, I recommend configuring two constituent division elements, Custom Date 01 and Custom Date 02 let's say, to be populated with constituents' most recent two distinct attendance dates.

Then in Analytics, a formula like this will return the overall average days between constituents' most recent two attendance dates.

AVG ( [Constituent ID] , MAX ( DDIFF ( [Days in Custom Date 01] , [Days in Custom Date 02] ) ) )

An alternative to Attended Dates is to use Order Dates if you're excluding sales prior to visit.

I had a fiddle with this and was able to perform the whole calculation in Analytics....

It is possible to create a formula that recognizes the rank of the Attended Date, and conditionally render that rank as 1 or 2, otherwise show nothing.

IF(
  RANK(
    MAX([Calendar Year] * 10000 + [Calendar Month] * 100 + [Calendar Day Of Month]), "DESC", "1223", [Constituent ID]

    ) = 1, 1, NULL
 )

And then enhance that formula to show a value form of the Attended Date formatted as the (Calendar Year * 365) + (Calendar Day of Year). With that formatting, it's reasonably accurate to subtract one date value from another and get the number of days in between. DDIFF() functions don't support applying filters in-line, so this numeric approach is necessary.

IF(
  RANK(
    MAX([Calendar Year] * 365 + [Calendar Day Of Year]), "DESC", "1223", [Constituent ID]
    ) = 1
  ,MAX([Calendar Year] * 365 + [Calendar Day Of Year])
  ,NULL
)

Proving out that a formula can reference the distinct results across the ranking, here their combined to look at the top 2 ranks and compare them... the first formula to get the days between the dates ranked 1 and 2. For each constituent's attended dates exists one with rank of 2, then subtract the date value for the attended date with rank of 1 from the attended date with rank of 2 to get the number of days between those two ranked attended dates. Otherwise, return nothing (NULL).

SUM([Constituent ID],
    IF(
        MAX([Days in Date],
            IF(
                RANK(
                    MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                    "DESC", "1223", [Constituent ID]
                ) = 2, MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                NULL
            )
        ) > 0
        , MIN([Days in Date],
            IF(
                RANK(
                    MIN([Calendar Year] * 365 + [Calendar Day Of Year]),
                    "DESC", "1223", [Constituent ID]
                ) = 1, MIN([Calendar Year] * 365 + [Calendar Day Of Year]), NULL
            )
        ) -
        MIN([Days in Date],
            IF(
                RANK(
                    MIN([Calendar Year] * 365 + [Calendar Day Of Year]),
                    "DESC", "1223", [Constituent ID]
                ) = 2, MIN([Calendar Year] * 365 + [Calendar Day Of Year]), NULL
            )
        )
        , NULL
    )
)

To take the average from across those days between dates, but with some special handling to avoid counting constituents who don't have the required 2 visits to be included in the metric. If the previous formula returns a not NULL value, then use that previous formula as the numerator and divide by the number of constituents having an attended date of rank 2 (excluding those with only single attendance). 

IF(
    SUM([Constituent ID],
        IF(
            MAX([Days in Date],
                IF(
                    RANK(
                        MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                        "DESC", "1223", [Constituent ID]
                    ) = 2, MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                    NULL
                )
            ) > 0,
            MIN([Days in Date],
                IF(
                    RANK(
                        MIN([Calendar Year] * 365 + [Calendar Day Of Year]),
                        "DESC", "1223", [Constituent ID]
                    ) = 1, MIN([Calendar Year] * 365 + [Calendar Day Of Year]), NULL
                )
            ) -
            MIN([Days in Date],
                IF(
                    RANK(
                        MIN([Calendar Year] * 365 + [Calendar Day Of Year]),
                        "DESC", "1223", [Constituent ID]
                    ) = 2, MIN([Calendar Year] * 365 + [Calendar Day Of Year]), NULL
                )
            ), NULL
        )
    ) > 0,
    SUM([Constituent ID],
        IF(
            MAX([Days in Date],
                IF(
                    RANK(
                        MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                        "DESC", "1223", [Constituent ID]
                    ) = 2, MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                    NULL
                )
            ) > 0,
            MIN([Days in Date],
                IF(
                    RANK(
                        MIN([Calendar Year] * 365 + [Calendar Day Of Year]),
                        "DESC", "1223", [Constituent ID]
                    ) = 1, MIN([Calendar Year] * 365 + [Calendar Day Of Year]), NULL
                )
            ) -
            MIN([Days in Date],
                IF(
                    RANK(
                        MIN([Calendar Year] * 365 + [Calendar Day Of Year]),
                        "DESC", "1223", [Constituent ID]
                    ) = 2, MIN([Calendar Year] * 365 + [Calendar Day Of Year]), NULL
                )
            ), NULL
        )
    ) 
    /
    SUM([Constituent ID],
        IF(
            MAX([Days in Date],
                IF(
                    RANK(
                        MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                        "DESC", "1223", [Constituent ID]
                    ) = 2, MAX([Calendar Year] * 365 + [Calendar Day Of Year]),
                    NULL
                )
            ) > 0, 1, NULL
        )
    ), NULL
)

It's weedy, but it works.

The dashboard is filtered by a list of constituents with tickets to the current and prior season fiscal years. The formulas should respect dashboard filters, so if there were to be filtered to only a Daily Admissions production season, the results should adjust as expected. The Constituent ID filter would need to be updated as well to reflect those changes, or switch to an actual Lists filter if that feels more effective.

RepeatCycle.dash

Well Chris, it took me a while to sift through the formulas and properly set the filters so I could validate the results. I think I need a nap now, but many thanks for figuring out how to get us to the data. I enjoyed learning your multiplying the attended year by 365 plus the attended day of year method for figuring out the number of days in between the dates.

It is still crazy to me that multiplying the year by exactly 365 (# of days in a year - usually) and then adding the Calendar Day of Year makes the math work out properly.  

MAX([Calendar Year] * 365 + [Calendar Day Of Year])

It took me a bit to realize that the formula is just converting the Years into Days so that you can compare days to days to compute the difference.