Columns Independently Filter

[StacyCui]

Hi, I have a table with three columns - Customer, Fiscal Year, and Product Combination. I would like to create a table that displays each customer's product combination for FY22 and FY23, and I want these two fields to be independently filterable. For example, I would like to know which customers have purchased A product in FY22 and B product in FY23. How can I write a formula for the product combination for both years?

Here comes my scripts: only({<FY={'FY23'}>}BU_Combination) 

But they're a measures, they could  not filter.

[vamshi2i]

Create a table that displays each customer's product combination for Fiscal Year (FY) 2022 and FY 2023 independently filterable, you can use set analysis in Qlik Sense expressions. Here's how you can achieve this:

Assuming you have a table with the following columns: Customer, FiscalYear, and ProductCombination.

1. Create a table with the Customer dimension and two measures: one for FY22 and one for FY23.

For FY22 product combination, use the following expression:

=aggr(

    if(FiscalYear = 'FY22', ProductCombination),

    Customer, FiscalYear, ProductCombination

)

For FY23 product combination, use the following expression:

=aggr(

    if(FiscalYear = 'FY23', ProductCombination),

    Customer, FiscalYear, ProductCombination

)

2. In the table visualization, use the "Customer" field as the dimension and add the two expressions above as measures.

Now, you should have a table that shows each customer's product combination for FY22 and FY23 separately. The measures use set analysis with the `aggr()` function to calculate the product combination based on the FiscalYear and Customer dimensions, and it filters the data accordingly for each year.

With this you can independently filter the table for FY22 and FY23 to see which customers purchased specific product combinations in each fiscal year.

[StacyCui]

Hi, I try the expression. But It doesn't work.