Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
varunkumar_mung
Contributor II
Contributor II

Dynamic year passing as parameter in pivot table

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

Labels (4)
3 Replies
vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
varunkumar_mung
Contributor II
Contributor II
Author

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
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.