Calculated field based on multiple fields and conditions

[onanm]

Hi,

I need to create a new field based on other fields and multiple conditions. I want to map 'Quarter' as either 'Q1' or 'Q2' or 'Q3' based on the date and company specific period. The challange here is date should not be taken into account as calendar date. Otherwise, it would have been pretty straightforward as March, June and Spetember would have been Q1, Q2 and Q3 respectively. 'Period' field helps us to identify year-end. For example if December is 'Year' then December is year-end and March is first quarter (Q1). On the other hand, if September is 'Year' for a company then September is year-end and December is first quarter (Q1) and March is second quarter (Q2) for that company. Below is a sample illustrative data I created. I need to create Quarter (th) column.

As you can see:

• Company 2 has December as 'Year', then March ıs Q1
• Company 7 has December as 'Year', then March is Q1 and September is 'Q3'
• Company 313 has March as 'Year', then June is Q1
• Company 322 has September as 'Year', then December is Q1, March is Q2 and June is Q3

Company	Date	Period	Quarter (th)
Company2	31.12.2016	Year
Company2	30.06.2017	Half-Year
Company2	31.12.2017	Year
Company2	30.06.2018	Half-Year
Company2	31.12.2018	Year
Company2	31.03.2019	Quarter	Q1
Company2	30.06.2019	Half-Year
Company2	31.12.2019	Year
Company2	30.06.2020	Half-Year
Company6	31.12.2017	Year
Company6	30.06.2018	Half-Year
Company6	31.12.2018	Year
Company6	31.03.2019	Quarter	Q1
Company6	30.06.2019	Half-Year
Company6	31.12.2019	Year
Company6	31.03.2020	Quarter
Company6	30.06.2020	Half-Year
Company7	31.12.2016	Year
Company7	31.03.2017	Quarter	Q1
Company7	30.06.2017	Half-Year
Company7	30.09.2017	Quarter	Q3
Company7	31.12.2017	Year
Company7	31.03.2018	Quarter	Q1
Company7	30.06.2018	Half-Year
Company7	30.09.2018	Quarter	Q3
Company7	31.12.2018	Year
Company7	31.03.2019	Quarter	Q1
Company7	30.06.2019	Half-Year
Company7	30.09.2019	Quarter	Q3
Company7	31.12.2019	Year
Company7	31.03.2020	Quarter	Q1
Company7	30.06.2020	Half-Year
Company64	31.03.2018	Quarter
Company64	30.06.2018	Quarter
Company64	31.12.2018	Year
Company64	31.03.2019	Quarter	Q1
Company64	30.06.2019	Quarter	Q2
Company64	30.09.2019	Quarter	Q3
Company64	31.12.2019	Year
Company64	31.03.2020	Quarter	Q1
Company64	30.06.2020	Quarter	Q2
Company64	30.09.2020	Quarter	Q3
Company74	31.12.2017	Year
Company74	31.12.2018	Year
Company74	31.03.2019	Quarter	Q1
Company74	30.06.2019	Quarter	Q2
Company74	30.09.2019	Quarter	Q3
Company74	31.12.2019	Year
Company74	31.03.2020	Quarter	Q1
Company74	30.06.2020	Quarter	Q2
Company74	30.09.2020	Quarter	Q3
Company77	31.12.2017	Year
Company77	31.03.2018	Quarter	Q1
Company77	30.06.2018	Half-Year
Company77	30.09.2018	YTD
Company77	31.12.2018	Year
Company77	30.09.2019	YTD
Company77	31.12.2019	Year
Company98	31.12.2017	Year
Company98	31.12.2018	Year
Company98	31.03.2019	Quarter	Q1
Company98	30.06.2019	Quarter	Q2
Company98	30.09.2019	Quarter	Q3
Company98	31.12.2019	Year
Company98	31.03.2020	Quarter	Q1
Company98	31.12.2020	Year
Company313	31.03.2017	Year
Company313	31.03.2018	Year
Company313	31.03.2019	Year
Company313	30.06.2019	Quarter	Q1
Company313	31.03.2020	Year
Company313	30.06.2020	Quarter	Q1
Company322	30.09.2016	Year
Company322	31.03.2017	Quarter	Q2
Company322	30.09.2017	Year
Company322	31.12.2017	Quarter	Q1
Company322	31.03.2018	Quarter	Q2
Company322	30.06.2018	Quarter	Q3
Company322	30.09.2018	Year
Company322	31.12.2018	Quarter	Q1
Company322	31.03.2019	Quarter	Q2
Company322	30.06.2019	Quarter	Q3
Company322	30.09.2019	Year
Company322	31.12.2019	Quarter	Q1
Company322	31.03.2020	Half-Year
Company322	30.06.2020	Quarter	Q3
Company322	30.09.2020	Year

Thanks a lot

[Qiu]

@onanm 
Hope this works for you.

[AngelosPachis · Accepted solution]

Hi @onanm ,

Maybe something like this? I have annotated the steps in the workflow to guide you through it

Hope that helps,

Angelos

[onanm]

Thanks a lot Angelos!

It worked well. However, I have slightly modified the workflow as I noticed that some companies in my dataset have multiple year-ends identified and some companies have not even year-end identified. In order to overcome former issue, I added an intermediary step to forcing year-end as maximum date having 'year' period. As a result, I have got unique year-end month for each company. Latter issue has been overcome by left outer join by having union of L and J.

Thanks again

[onanm]

Thanks a lot Qio,

Though it worked well, I employed a bit more convenient approach proposed by Angelos. Besides, some companies do no have unique year-end months and some companies even do not have year-end month identified. I figured out these issues.

Thanks again for your kind help