topic Re: Year Over Year Counts in QlikView

Year Over Year Counts

raynac — Tue, 02 Dec 2025 18:28:38 GMT

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!

Re: Year Over Year Counts

rubenmarin — Tue, 02 Dec 2025 18:37:51 GMT

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)

Re: Year Over Year Counts

Nagaraju_KCS — Wed, 03 Dec 2025 06:42:07 GMT

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.

Re: Year Over Year Counts

rubenmarin — Wed, 03 Dec 2025 07:57:41 GMT

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]
)

Re: Year Over Year Counts

raynac — Wed, 03 Dec 2025 19:59:30 GMT

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!

Re: Year Over Year Counts

raynac — Wed, 03 Dec 2025 20:01:15 GMT

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.