Creating Reporting Period

[Thabang231]

Hi Team,

I would like your assistance I have a field called Date with format 2024/05/26 as e.g. and I would like to create a new field in the load editor using this current field to give me values:

July 2023 - June 2024

July 2024 - June 2025

July 2025 - June 2026, this values will then be in the filter in the front end to be selected. 

Your help is appreciated, thanks in advance

Regards

[Qrishna · Accepted solution]

try below:

Data:
Load Date#(Date, 'YYYY/MM/DD') as Date,
Num(Date#(Date, 'YYYY/MM/DD')) as DateNum,
Year(Num(Date#(Date, 'YYYY/MM/DD'))) as Yr;
Load * inline [
Date
2024/05/26
];

left join(Data)
d:
load Date,
Text(Month(AddMonths(DateNum, 2)) & ' ' & (Yr + IterNo()-2) & ' - ' & Month(AddMonths(DateNum, 1)) & ' ' & (Yr + IterNo()-1)) as Filter
resident Data
while IterNo() <= 3;

drop field DateNum, Yr;

exit script;