Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
StacyCui
Creator
Creator

Columns Independently Filter

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.

StacyCui_0-1691042939145.png

 

Labels (3)
2 Replies
vamshi2i
Contributor II
Contributor II

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
Creator
Creator
Author

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

StacyCui_0-1691051167193.png