Unique Rolling Years Based on Another Field

[chris_92]

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:

StateQuarterCombo	Rolling Year	WAyear.idx	WYyear.idx
WA|2020Q1	Q2 2019 - Q1 2020	2
WA|2019Q4	Q2 2019 - Q1 2020	2
WA|2019Q3	Q2 2019 - Q1 2020	2
WA|2019Q2	Q2 2019 - Q1 2020	2
WA|2019Q1	Q2 2018 - Q1 2019	1
WA|2018Q4	Q2 2018 - Q1 2019	1
WA|2018Q3	Q2 2018 - Q1 2019	1
WA|2018Q2	Q2 2018 - Q1 2019	1
WY|2020Q3	Q4 2019 - Q3 2020		2
WY|2020Q2	Q4 2019 - Q3 2020		2
WY|2020Q1	Q4 2019 - Q3 2020		2
WY|2019Q4	Q4 2019 - Q3 2020		2
WA|2019Q1	Q4 2018 - Q3 2019		1
WA|2018Q4	Q4 2018 - Q3 2019		1
WA|2018Q3	Q4 2018 - Q3 2019		1
WA|2018Q2	Q4 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!

[edwin]

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. 

[edwin]

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