Year Over Year Counts

[raynac]

Hi all!

I am trying to build a chart that shows every client who has one or more bookings in the "Chosen" year.  Subsequently, I'd like to show, from that set, if those clients were return guests... ie if they had bookings over the previous year and/or the one before that.  So I want only the clients in the bottom chart. 

However, I have tried Alternate States and Set Analysis to try and make this work, and every time I add in the previous years, it gives me any client who had a booking in either of these two years, regardless of whether they had one in the year that I am trying to focus on.

Can someone please point me towards the formula I'd use to keep the chart from including clients that didn't book in the chosen year?

Thank you!

[rubenmarin · Accepted solution]

Hi, you have at least to options:

- Use a calculated dimension checking the option to supress when value is null, the calcualted dimension should filter customers with booking on the chosen year, like:

Aggr(If([ContionToCheckCustomerOnChosenYear], Customer), Customer)

- Filter other expressions, to only calculate when ChosenYear>0, i.e. 1 year prev could be:

If({Chosen Year]>0, [ExpressionFor1YearPrev)

[Nagaraju_KCS]

May be try this

=Count(
{<[Client Code] = {"=BookingYear = $(vChosenYear)"}>}
*{<[Client Code] = {"=BookingYear = $(vChosenYear)-1"}>}
[Client Code]
)

* Intersection Operator: ONLY include clients who meet BOTH criteria.

[rubenmarin]

Hi, to use that option you should remove the } before * and the { after *. Operations is betwen <> segments or field values, inside the same set analysis.

And maybe you need to use P():

=Count(
{<[Client Code] = P({<BookingYear={$(vChosenYear)} [Client Code])>
*<[Client Code] = P({<BookingYear={"=$(vChosenYear)-1"} [Client Code])>}
[Client Code]
)

[raynac · Accepted solution]

Thank you SO much, Ruben!  I used the first option and it worked perfectly, once I used a variable.  Wound up doing this:

=Aggr(If([Arrival Year]=$(vChosenYear) , client_code), client_code)

And I set the variables using an OnSelect trigger in the Arrival Year field. 

Then I used $(vChosenYear-1) and $(vChosenYear-2) in Set Analysis for the other two year columns.  That may be more complicated than it needed to be, but it's working which is what matters!

Thank you again!

[raynac]

Thank you so much for your help here.  Your idea to use a variable (I use them so infrequently) really made this come together, along with Ruben's advice.  This was truly a group effort and I appreciate you both.