Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
Showing results for 
Search instead for 
Did you mean: 

Unique Rolling Years Based on Another Field

Hello all:

I'm fairly new to app development, and this is my first time posting on the forums, so please excuse any errors/exclusions.

I am trying to build a table in my data model that will link to my main data table that provides unique rolling years for multiple states, along with a year index that can be used in calculations in my app. My ideal table would look something like this, with the StateQuarterCombo column being the key field that was previously calculated:

StateQuarterComboRolling YearWAyear.idxWYyear.idx
WA|2020Q1Q2 2019 - Q1 20202 
WA|2019Q4Q2 2019 - Q1 20202 
WA|2019Q3Q2 2019 - Q1 20202 
WA|2019Q2Q2 2019 - Q1 20202 
WA|2019Q1Q2 2018 - Q1 20191 
WA|2018Q4Q2 2018 - Q1 20191 
WA|2018Q3Q2 2018 - Q1 20191 
WA|2018Q2Q2 2018 - Q1 20191 
WY|2020Q3Q4 2019 - Q3 2020 2
WY|2020Q2Q4 2019 - Q3 2020 2
WY|2020Q1Q4 2019 - Q3 2020 2
WY|2019Q4Q4 2019 - Q3 2020 2
WA|2019Q1Q4 2018 - Q3 2019 1
WA|2018Q4Q4 2018 - Q3 2019 1
WA|2018Q3Q4 2018 - Q3 2019 1
WA|2018Q2Q4 2018 - Q3 2019 1


Unfortunately, I haven't had much luck with any coding I've tried so far. Would you have any tips on how I might start to construct this?

Thank you very much!

2 Replies
Specialist III
Specialist III

you must have a formula that when you have a combination of StateQuarter + WAYear + WYYear will result in quarters/year.  you can then concatenate the 3 key fields into a new key field and associate that with individual quarter Year:
Key Quarter
WA|2020Q1|2| Q2 2019
WA|2020Q1|2| Q3 2019 
WA|2020Q1|2| Q4 2019
WA|2020Q1|2| Q1 2020
so your fact will have this key, your bridge will have the key and quarter

so when the state, WA Year and WY Year is selected, it will automatically associate with the desired quarter year. 

Specialist III
Specialist III

it becomes a little complicated when your fact is already linked to your calendar.  the current link needs to be taken into consideration.  so the level of complication depends on the current data model