Create a list of value for fiscal year period

random_user_3869 · ‎2022-04-07

Hello to everyone,

In my qlik saas app i have the following calendar:

$(v_TableName):
LOAD DISTINCT
Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY') AS [Date Creation Cde],
Year(Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY')) AS [Annee_C],
Month(Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY')) AS [Mois_C],
pick(Match(Month(Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY')),
'janv.','févr.','mars','avr.','mai','juin','juil.','août','sept.','oct.','nov.','déc.'),
01,02,03,04,05,06,07,08,09,10,11,12) as [Mois_C_Num],
Day(Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY')) AS [Jour_C],
Monthname(Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY')) AS [Annee Mois_C],
'S'&Ceil(Month(Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY'))/6) as [Semestre_C],
if (Monthname(Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY'))<='31/03/2019' and Monthname(Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY'))>='01/04/2018', 'Exercice 2018-2019',
if (Monthname(Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY'))<='31/03/2020' and Monthname(Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY'))>='01/04/2019', 'Exercice 2019-2020',
if (Monthname(Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY'))<='31/03/2021' and Monthname(Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY'))>='01/04/2020', 'Exercice 2020-2021',
if (Monthname(Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY'))<='31/03/2022' and Monthname(Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY'))>='01/04/2020', 'Exercice 2021-2022',
if (Monthname(Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY'))<='31/03/2023' and Monthname(Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY'))>='01/04/2022', 'Exercice 2022-2023',
if (Monthname(Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY'))<='31/03/2024' and Monthname(Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY'))>='01/04/2023', 'Exercice 2023-2024',
if (Monthname(Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY'))<='31/03/2025' and Monthname(Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY'))>='01/04/2024', 'Exercice 2024-2025'))))))) as [Exercice comptable_C],
NetWorkDays(Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY'),Date(Date#(right([B0GLDT],6), 'YYMMDD') ,'DD/MM/YYYY')) as Jours_Ouvres_C
FROM [$(vs_QVD)/$(vs_QVD_Prefixe_Niv1)_ORB0REP.qvd]
(qvd);

I would like to simply the if condition regarding the field [Exercice comptable_C] (f = fiscal year)

I would like to formulate a simplier way (a syntax such as an automatic list of value let's say if we are year 1 then fiscal year period 2020-2021, if year 2 then fiscal year 2021-2022)

I imagine something with a each ... but i don't know

Thank you

Qbo · ‎2022-04-07

Create a fiscal year table and then you can IntervalMatch() the date periods back to it. So remove them and do something like this:

FiscalYear:
LOAD Start,
     End,
     'Exercise '&Start&' - '&End as FiscalYear INLINE [
End, Start
31/03/2019,01/04/2018
31/03/2020,01/04/2019
31/03/2021,01/04/2020
31/03/2022,01/04/2021
];

Inner Join IntervalMatch ([Date Creation Cde])
 
LOAD Start, End
Resident FiscalYear;

random_user_3869 · ‎2022-04-07

Thank you A LOT 🙂

General Question