Filtering rows without remove from the table

[Greg3]

Hi, 

I am quite a new in Qlik, and I have an issue which I can't solve yet, so I would like to ask help from more experts people. 

I try to explain the simplest way I can. 

I only want presenting Agencies (by Index number - Index_AgencyID the field name) who has 0 bookings in the selected year(s). 

I use this as a solution: 
=aggr(only({$<[Index_AgencyID]={count(Index_BookingsID)=0"}>} [Index_AgencyID]), [Index_AgencyID]) 

But unfortunately with this, I exclude every Agency with bookings, and I only want to exclude those agencies who don't have bookings for the selected year(s). So maybe 1 agency only has 1 booking in 2020, so in that case if I choose 2021, I want to see it in the table, but if I choose 2020 then I don't want to. 

Thank you in advance!

[Greg3]

@Kushal_Chawda Here the first table is where I use what you suggested, at the second I don't use nothing. You can see the differences. 

I don't want to modify the numbers to presenting at every agency has 0 bookings. 
For example, you can see in the second table AgencyID 7, it has 0 bookings for 2023  (this is the year I selected) but maybe if I choose 2022 it would disappear because they have at least 1 booking for 2022. This is what I want. 

I used this originally : =aggr(only({$<[Index_AgencyID]={"=count(Index_BookingsID)=0"}>} [Index_AgencyID]), [Index_AgencyID])

But it removes every agency with bookings for the 5 years (2019-2023) but I don't want this. I want that if I select a year or years, the agencies for that period that have 0 bookings should be displayed. Do not display agencies that have bookings for the given period.

Do you have any idea?

Thank you for your helping! 

[Kushal_Chawda]

@Greg3  Not sure what expression you are using and how underlying data is but with expression I provided AgencyID 19 should not have included in the report because it has bookings for the selected year.

see the example below. I have created dummy data

Data:
Load * Inline [
AgencyID, Bookings, Year
7,0,2023
8,0,2023
19,18,2023
7,10,2022];

Now, when I select 2023, I get only 7,8 because in that year both has zero

Now, when I select both 2022,2023. I only get 8 because it has 0 bookings for the selected period. 7 has booking in 2022 so it is not included.

[Greg3]

@Kushal_Chawda So I think the issue should come from my data or something like that.

My booking numbers are displayed via a variable called "vAllBookings" and before my task the bookings were presented like this

=$(vAllBookings) 

And the variable equal with this function, this is behind the variable. 

Sum(Index_BookingsID_All)

Fortunately I can create and modify variables. 

I can't get what is that Index_BookingsID_ALL , it looks like this when I disaplayed it by itself, and next to it on the left side I using the vAllBookings

I used what you suggested like this :

aggr(Sum({<[Index_AgencyID]={ "=sum(Index_BookingsID_All)=0"}>} $(vAllBookings)),$(vAllBookings))

And it looks like this in the table

But I checked randomly picked Agencies, and they have bookings for 2023 (this is the selected year)

I tried like this too 

=Sum({<[Index_AgencyID]={ "=Sum(Index_BookingsID_All)=0"}>} Index_BookingsID_All)

And it looks like this, I put at the last column the vAllBookings, just to see that sadly it's not good 

Please any suggestion ?

Thanks in advance!

[Kushal_Chawda]

@Greg3  I think you just need Bookings column with below expression.  Keep AgencyID, Searches & Bookings. Remove other column..

=Sum({<[Index_AgencyID]={ "=Sum(Index_BookingsID_All)=0"}>} Index_BookingsID_All)