Solved: Fiscal Year Selection Optimization - Qlik Community

melissapluke · ‎2020-10-28

Hi,

I have a table with the field 'Routine Schedule' in it which is based on fiscal year. Fiscal year starts in July.

These are the options for that field.

'Even FY-Q1','Even FY-Q2','Even FY-Q3','Even FY-Q4',
'Odd FY-Q1','Odd FY-Q2','Odd FY-Q3','Odd FY-Q4'

For reference I have a variable in my script called vFM=7 already used in other parts of my script.

Based on that 'Routine Schedule' field, I have the following set up in my script. I am trying to add a future scheduled date based on that routine schedule field. This script works. However, I need to go into the script starting 7/1/2021 on a quarterly basis and update the values I am assigning. on July 1, 2021, I would change the date picked with Even FY-Q1 to '07/01/2023'. On October 1, 2021, I would change the date picked with 'Even FY-Q2' to '10/01/2023'...the pattern continues throughout all 8 selections. Is there any way to optimize this to automatically assign the date or at least the year within the date so I don't have to come in to adjust this each quarter?

left join (Assignments)
Load
"Routine Schedule",
Pick(Match("Routine Schedule",
'Even FY-Q1','Even FY-Q2','Even FY-Q3','Even FY-Q4',
'Odd FY-Q1','Odd FY-Q2','Odd FY-Q3','Odd FY-Q4'),
'07/01/2021','10/01/2021','12/01/2022','04/01/2022',
'07/01/2022','10/01/2022','12/01/2023','04/01/2023') as "Future Schedule Date",
Pick(Match("Routine Schedule",
'Even FY-Q1','Even FY-Q2','Even FY-Q3','Even FY-Q4',
'Odd FY-Q1','Odd FY-Q2','Odd FY-Q3','Odd FY-Q4'),
'FY22-Q1','FY22-Q2','FY22-Q3','FY22-Q4',
'FY23-Q1','FY23-Q2','FY23-Q3','FY23-Q4') as "Future Schedule Year-Quarter"
Resident Assignments;

Thanks for the help!

Melissa

Kushal_Chawda · ‎2020-11-08

@melissapluke try below

Assuming that you need to change the Dates when you are arriving in month of July for particular year. Hence, I have applied the condition on month of July.

if month(Today())= 'Jul' and Mod(Year(Today()),2)=0 THEN

Map:
CrossTable("Routine Schedule","Future Schedule Date")
LOAD RowNo() as Row,
     quarterstart(addyears(YearStart(Today(),0,7),2),0,7) as [Odd FY-Q1],
     quarterstart(addyears(YearStart(Today(),0,7),2),1,7) as [Odd FY-Q2],
     quarterstart(addyears(YearStart(Today(),0,7),2),2,7) as [Odd FY-Q3],
     quarterstart(addyears(YearStart(Today(),0,7),2),3,7) as [Odd FY-Q4],
     quarterstart(addyears(YearStart(Today(),0,7),1),0,7) as [Even FY-Q1],
     quarterstart(addyears(YearStart(Today(),0,7),1),1,7) as [Even FY-Q2],
     quarterstart(addyears(YearStart(Today(),0,7),1),2,7) as [Even FY-Q3],
     quarterstart(addyears(YearStart(Today(),0,7),1),3,7) as [Even FY-Q4]
AutoGenerate 1;

elseif month(Today())= 'Jul' and Mod(Year(Today()),2)<>0 THEN

Map:
CrossTable("Routine Schedule","Future Schedule Date")
LOAD RowNo() as Row,
     quarterstart(addyears(YearStart(Today(),0,7),2),0,7) as [Even FY-Q1],
     quarterstart(addyears(YearStart(Today(),0,7),2),1,7) as [Even FY-Q2],
     quarterstart(addyears(YearStart(Today(),0,7),2),2,7) as [Even FY-Q3],
     quarterstart(addyears(YearStart(Today(),0,7),2),3,7) as [Even FY-Q4],
     quarterstart(addyears(YearStart(Today(),0,7),1),0,7) as [Odd FY-Q1],
     quarterstart(addyears(YearStart(Today(),0,7),1),1,7) as [Odd FY-Q2],
     quarterstart(addyears(YearStart(Today(),0,7),1),2,7) as [Odd FY-Q3],
     quarterstart(addyears(YearStart(Today(),0,7),1),3,7) as [Odd FY-Q4]
AutoGenerate 1;

ENDIF

Note: Column names in above should match with exact value of "Routine Schedule" field

Then you can use the mapping load for both "Date" & "Quarter Year" like below

Map_future_date:
mapping LOAD "Routine Schedule",
     "Future Schedule Date"
Resident Map;

Map_future_quarter_year:
mapping LOAD "Routine Schedule",
     dual('FY'&date(date#(SubField(yearname("Future Schedule Date",0,7),'-',2),'YYYY'),'YY')&
     '-'&SubField("Routine Schedule",'-',2),"Future Schedule Date") as "Future Schedule Year-Quarter"
Resident Map;

DROP Table Map;

Now, use applymap in your actual data like below

Data:
LOAD *,
     ApplyMap('Map_future_date',"Routine Schedule") as "Future Schedule Date",
     ApplyMap('Map_future_quarter_year',"Routine Schedule") as "Future Schedule Year-Quarter"
FROM Table;

View solution in original post

Brett_Bleess · ‎2020-10-28

Have a look at the following Design Blog post and see if that might work for you:

https://community.qlik.com/t5/Qlik-Design-Blog/Fiscal-Year/ba-p/1472103

Might require a bit of rework, but hopefully may be a better solution for you.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.

melissapluke · ‎2020-10-28

Hi,

I have calendars set up just like that in my app which are associated with dates. I don't believe I can do that for this field though, because the routine schedule on the record isn't something that is directly associated with a date. It is assigned to a record based on where that record is in the organization and it doesn't have anything to do with an actual date associated with that record.

Kushal_Chawda · ‎2020-10-28

@melissapluke how do you recognize the year for "Routine Schedule"? What is the concept of Odd and Even ?

melissapluke · ‎2020-10-28

@Kushal_Chawda Year can be calculated off of today(). Odd and Even are related to the fiscal years. A record is assigned to either an odd year or even year and a certain quarter via the routine schedule in another table based on other criteria. So basically once they get into this schedule, they will get on a routine of having a future schedule date every 2 years. The fiscal year begins on July 1. So july 1 2020 was the start of Fiscal year 21, an odd year. July 1 2021 is an even year. If someone is assigned to even year Q1, they will be eventually scheduled for July 1, 2021, then July 1 2023, July 1, 2025 and so on. But they can only have 1 future scheduled date at a time. Does that help explain things?

Brett_Bleess · ‎2020-11-05

@Kushal_Chawda Just adding another comment, as I know notifications are acting weird again, so making sure you knew Melissa did reply back to your last post. Thanks in advance if you can swing back by when you have time to have a look. I am afraid I am not much help at this point.

Cheers,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.

Kushal_Chawda · ‎2020-11-07

@melissapluke When you are in Jul 2021 what will be the 'Even FY-Q1' & 'Odd FY-Q1' value? Also if you are in Jul 2022 then what will be the 'Even FY-Q1' & 'Odd FY-Q1' value?

melissapluke · ‎2020-11-08

Hi, as soon as we hit July 2021 everything would switch to this:

Pick(Match("Routine Schedule",
'Even FY-Q1','Even FY-Q2','Even FY-Q3','Even FY-Q4',
'Odd FY-Q1','Odd FY-Q2','Odd FY-Q3','Odd FY-Q4'),
'07/01/2023','10/01/2023','12/01/2024','04/01/2024',
'07/01/2022','10/01/2022','12/01/2023','04/01/2023')

As soon as we hit 7/1/2022, it would change to this:

Pick(Match("Routine Schedule",
'Even FY-Q1','Even FY-Q2','Even FY-Q3','Even FY-Q4',
'Odd FY-Q1','Odd FY-Q2','Odd FY-Q3','Odd FY-Q4'),
'07/01/2023','10/01/2023','12/01/2024','04/01/2024',
'07/01/2024','10/01/2024','12/01/2025','04/01/2025')

Kushal_Chawda · ‎2020-11-08