Dynamic year passing as parameter in pivot table

[varunkumar_mung]

I have a dummy table in data model with certain years and some important dates against it which is not linked to my fact table. i have created a pivot table which has data from fact table. I need to get the year from date in pivot table and check the year in the dummy table to get those dates in my pivot table. 

I used set analysis with aggr(). unfortunately, it does not work.

max({<Type={'RAM'},Low_Year={"(=aggr(Year([Policy From date]),[Policy No]))"}>}Low_Month_Start_Date)

low_year and low_month_start_date are from dummy table 

[policy from date] from fact table.

please help!!!

[vinieme12]

You need to create a calendar that is associated with the fact table 

Refer below on How to create a calendar

https://qlikviewcookbook.com/2015/05/better-calendar-scripts/

After that you can refer below post on how to filter data for particular periods 

https://community.qlik.com/t5/Qlik-Design-Blog/Dates-in-Set-Analysis/ba-p/1472511

[varunkumar_mung]

Thanks for the reply. But i cannot link year with fact table as there is no unique key between the tables.

as an example:- based on the value of policy from date and condition it has to take different sets from dummy table

if my policy starts on 01-01-2022 and end date is on 31-12-2022 i need to get the corresponding dates from dummy table for year 2022.

but if my policy start date is on 01-12-2021 and end date is on 31-11-2022, again i need to get dates for year 2022.

thats why i kept the dummy table as stand alone and trying to pass parameter year of from date or to date according to scenario 

[vinieme12]

If you have 2  or date fields in your fact table you just need to create a link table / intermediate table to associate it to the calendar

https://community.qlik.com/t5/Qlik-Design-Blog/Connecting-Multiple-Calendar-Date-Fields-in-a-Qlik-Se...