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!

[Kushal_Chawda]

@Greg3  could you share sample data with expected output?

[Greg3]

@Kushal_Chawda 

I hope it's enough for the solution, anyway thanks for the interest!

[Kushal_Chawda]

@Greg3  what if one agency has booking in year 2021 and don't have any booking in 2022. If you select 2021 and 2022 , would you still like to see that agency?

[Greg3]

@Kushal_Chawda In that case I don't want to see it, just only if I choose 2022, Of course if it's possible to present agencies this way. 

[Kushal_Chawda]

@Greg3  try below expression for Bookings. Make Sure Include Zero value option checked

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

[krishna_2644]

Tmp:
Load * Inline [
Index_AgencyID , Bookings, Year
A, 3, 2018
A, 0, 2019
A, 0, 2020
A, 1, 2021
A, 3, 2022
A, 5, 2023
B, 2, 2016
B, 0, 2018
B, 1, 2019
B, 3, 2020
B, 0, 2021
B, 0, 2022
B, 7, 2023 ];

Year:
Load distinct Year
Resident Tmp;

NoConcatenate
Out:
Load *
resident Tmp
where Bookings > 0;

Qualify *;
Unqualify Year;
Data:
Load *
Resident Tmp;
drop table Tmp;

[Greg3]

@Kushal_Chawda Thanks for the advice! I tried it, unfortunately it's just put zeros for the column "Bookings". I checked one of the agency and they have bookings for that specific year I choose. 

Could you please maybe advise something else ? 

Sorry if I ask a difficult question, sadly I don't have access for the Database, and can't create script, just only chart functiont like Set analysis, FYI.

[Greg3]

@krishna_2644  Thanks for the interests, I am not sure what you want to say with this, sorry. I don't have access to the Database and  I can only creating chart functions, if I saying it right. 

[Kushal_Chawda]

@Greg3  My logic will show only zero booking for the year you have selected, so you will get zero values for the bookings. Is it not you want? In your screenshot, you have shown zero in Bookings when year is selected.