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