Format Quarter Date like: 21/22 - Q1

[JacobJones]

How can I change the calendar in my load script to show Quarters, which start in April, with the two years the fiscal year spans? Also I only want the last two digits of the year.

Thank you.

[JordyWegman]

Hi Jacob,

A bit of data would help on how your calendar looks. But conceptually you could just create another column with a concatenation of the two fields. You would need to calculate first which years are connected to the quarters, more like a min/max.

As mentioned, if you have some data on how your calendar looks, a solution would be easy I think.

Jordy

Climber

[JacobJones]

Hi Jordy,

Thanks for the reply, here is my calendar. Sorry about the formatting, having trouble making it look nice.

I am using YearQuarter and right now the format is 2021- Q1 

LET vFM = 4;
[calendar]:

DECLARE FIELD DEFINITION Tagged ('$date')

FIELDS
Dual(Right(YearStart($1,0,vFM),2)&'/'&(Right(YearStart($1,0,vFM),2)+1), YearStart($1,0,vFM)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month(AddMonths($1,1-vFM)))/3)),Num(Ceil(NUM(Month(AddMonths($1,1-vFM)))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month(AddMonths($1,1-vFM)))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month(AddMonths($1,0,1-vFM)))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Dual(Month($1), Month($1)) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Year($1), YearStart($1,0,vFM)) AS [Year2] Tagged ('$axis', '$year'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified');


DERIVE FIELDS FROM FIELDS

[JacobJones · Accepted solution]

If anyone is looking for an answer to this, this works:

LET vFM = 4;

Dual( If(Num(Ceil(Num(Month(AddMonths($1,1-vFM)))/3))=4,Right(Year($1),2)-1 & '/' & Right(Year($1),2) , Right(Year($1),2) & '/' & Right(Year($1)+1,2)) &'-Q'& Num(Ceil(Num(Month(AddMonths($1,1-vFM)))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),