topic Dynamic year passing as parameter in pivot table in App Development

Dynamic year passing as parameter in pivot table

varunkumar_mung — Sun, 08 May 2022 12:38:02 GMT

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!!!

Re: Dynamic year passing as parameter in pivot table

vinieme12 — Sun, 08 May 2022 17:46:15 GMT

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

Re: Dynamic year passing as parameter in pivot table

varunkumar_mung — Mon, 09 May 2022 07:12:23 GMT

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

Re: Dynamic year passing as parameter in pivot table

vinieme12 — Mon, 09 May 2022 09:21:17 GMT

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-Sense-App/ba-p/1805087Then just create an intermediate table