Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I am having quarterly data from which I need to create MAT calculations. I don’t have date field only the information available is year, quarter (Q1, Q2 and so on) and one field is qtr year (2020 - Q1, 2020 - Q2 and so on). If I use MAX function qtr won’t pick up, if I use MAXSTRING we can’t get previous quarter sum up. Please help me it’s urgent
Hi @nitesh2730
Thank you for the sample data, actually you have all what is needed to build a rtDate (run time date) with those columns for each quarter, because we know the month corresponding to each one of them; this mapping table is able to convert a quarter string to a numeric month:
Mapping_QtrMon:
Mapping Load * Inline [
Qtr, Month
Q1, 1
Q2, 4
Q3, 7
Q4, 10
];
We reference this mapping table when loading your source data to create a rtDate, as shown below:
Load *,
MakeDate("Year", ApplyMap('Mapping_QtrMon', Quarter)) As rtDate
Inline [
Seq,Qtr-Year,Quarter,Year,Data
1,2013 - Q1,Q1,2013,100
16,2013 - Q1,Q1,2013,50
2,2013 - Q2,Q2,2013,75
15,2013 - Q2,Q2,2013,25
3,2013 - Q3,Q3,2013,200
14,2013 - Q3,Q3,2013,100
4,2013 - Q4,Q4,2013,175
13,2013 - Q4,Q4,2013,125
5,2014 - Q1,Q1,2014,100
12,2014 - Q1,Q1,2014,50
6,2014 - Q2,Q2,2014,75
11,2014 - Q2,Q2,2014,25
7,2014 - Q3,Q3,2014,200
10,2014 - Q3,Q3,2014,100
8,2014 - Q4,Q4,2014,175
9,2014 - Q4,Q4,2014,125
];
I added some dummy records for the year 2014, and the quarters: Q1, Q2, Q3 and Q4 for 2013 and 2014; this line in the script creates the rtDate.
MakeDate("Year", ApplyMap('Mapping_QtrMon', Quarter)) As rtDate
Now, as we have a proper date for these transaction we could apply a DUAL function to your Qtr-Year and Quarter columns, which should allow you to go ahead using them as you expect.
MAT_Data:
Load
Seq,
Dual("Qtr-Year",QuarterStart(rtDate)) As "Qtr-Year",
Dual(Quarter,Num(ApplyMap('Mapping_QtrMon', Quarter),00)) As Quarter,
"Year" as "Year",
Data,
rtDate;
Tip:
When we use the Data Manager, it automatically generate an autocalendar for all the date fields, I took that section of the data manager as a code reference, so anytime I need to implement a date function it helps me a lot.
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Dual(Year($1) & '-' & Num(Week($1),00), Num(Year($1) * 100 + Week($1),00)) AS [YearWeek] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD_Yearless] ,
If (Year($1) = Year(Today()) And
DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD_Yearless] ,
If (Year($1) = Year(Today()) and
Num(Ceil(Num(Month($1))/3)) = Num(Ceil(Num(Month(Today()))/3)) and
DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [_QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD_Yearless] ,
If(Year($1) = Year(Today()) And
Month($1) = Month(Today()) And
Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [_MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD_Yearless] ,
If(Year($1) = Year(Today()) And
Month($1) = Month(Today()) And
Week($1) = Week(Today()) And
WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [_WeekRelNo],
Dual(MonthsEnd(1, $1), MonthsEnd(1, $1)) As [RY_End],
Dual(MonthStart( AddMonths( MonthsEnd(1, $1), -11)), MonthStart( AddMonths( MonthsEnd(1, $1), -11))) As [RY_Start]
I just replace the variable $1 for the date column, when I copy any of these expressions.
Hope this helps,
Hi @nitesh2730
It will helps if you share a sample of your data, I suspect you need to implement the Year-Q1 dimension as dual.
hth
below is the screenshot of fields
Hi @nitesh2730
Thank you for the sample data, actually you have all what is needed to build a rtDate (run time date) with those columns for each quarter, because we know the month corresponding to each one of them; this mapping table is able to convert a quarter string to a numeric month:
Mapping_QtrMon:
Mapping Load * Inline [
Qtr, Month
Q1, 1
Q2, 4
Q3, 7
Q4, 10
];
We reference this mapping table when loading your source data to create a rtDate, as shown below:
Load *,
MakeDate("Year", ApplyMap('Mapping_QtrMon', Quarter)) As rtDate
Inline [
Seq,Qtr-Year,Quarter,Year,Data
1,2013 - Q1,Q1,2013,100
16,2013 - Q1,Q1,2013,50
2,2013 - Q2,Q2,2013,75
15,2013 - Q2,Q2,2013,25
3,2013 - Q3,Q3,2013,200
14,2013 - Q3,Q3,2013,100
4,2013 - Q4,Q4,2013,175
13,2013 - Q4,Q4,2013,125
5,2014 - Q1,Q1,2014,100
12,2014 - Q1,Q1,2014,50
6,2014 - Q2,Q2,2014,75
11,2014 - Q2,Q2,2014,25
7,2014 - Q3,Q3,2014,200
10,2014 - Q3,Q3,2014,100
8,2014 - Q4,Q4,2014,175
9,2014 - Q4,Q4,2014,125
];
I added some dummy records for the year 2014, and the quarters: Q1, Q2, Q3 and Q4 for 2013 and 2014; this line in the script creates the rtDate.
MakeDate("Year", ApplyMap('Mapping_QtrMon', Quarter)) As rtDate
Now, as we have a proper date for these transaction we could apply a DUAL function to your Qtr-Year and Quarter columns, which should allow you to go ahead using them as you expect.
MAT_Data:
Load
Seq,
Dual("Qtr-Year",QuarterStart(rtDate)) As "Qtr-Year",
Dual(Quarter,Num(ApplyMap('Mapping_QtrMon', Quarter),00)) As Quarter,
"Year" as "Year",
Data,
rtDate;
Tip:
When we use the Data Manager, it automatically generate an autocalendar for all the date fields, I took that section of the data manager as a code reference, so anytime I need to implement a date function it helps me a lot.
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Dual(Year($1) & '-' & Num(Week($1),00), Num(Year($1) * 100 + Week($1),00)) AS [YearWeek] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD_Yearless] ,
If (Year($1) = Year(Today()) And
DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD_Yearless] ,
If (Year($1) = Year(Today()) and
Num(Ceil(Num(Month($1))/3)) = Num(Ceil(Num(Month(Today()))/3)) and
DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [_QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD_Yearless] ,
If(Year($1) = Year(Today()) And
Month($1) = Month(Today()) And
Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [_MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD_Yearless] ,
If(Year($1) = Year(Today()) And
Month($1) = Month(Today()) And
Week($1) = Week(Today()) And
WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [_WeekRelNo],
Dual(MonthsEnd(1, $1), MonthsEnd(1, $1)) As [RY_End],
Dual(MonthStart( AddMonths( MonthsEnd(1, $1), -11)), MonthStart( AddMonths( MonthsEnd(1, $1), -11))) As [RY_Start]
I just replace the variable $1 for the date column, when I copy any of these expressions.
Hope this helps,
I forgot to include the whole script:
Mapping_QtrMon:
Mapping Load * Inline [
Qtr, Month
Q1, 1
Q2, 4
Q3, 7
Q4, 10
];
NoConcatenate
MAT_Data:
Load
Seq,
Dual("Qtr-Year",QuarterStart(rtDate)) As "Qtr-Year",
Dual(Quarter,Num(ApplyMap('Mapping_QtrMon', Quarter),00)) As Quarter,
"Year" as "Year",
Data,
rtDate;
Load *,
MakeDate("Year", ApplyMap('Mapping_QtrMon', Quarter)) As rtDate
Inline [
Seq,Qtr-Year,Quarter,Year,Data
1,2013 - Q1,Q1,2013,100
16,2013 - Q1,Q1,2013,50
2,2013 - Q2,Q2,2013,75
15,2013 - Q2,Q2,2013,25
3,2013 - Q3,Q3,2013,200
14,2013 - Q3,Q3,2013,100
4,2013 - Q4,Q4,2013,175
13,2013 - Q4,Q4,2013,125
5,2014 - Q1,Q1,2014,100
12,2014 - Q1,Q1,2014,50
6,2014 - Q2,Q2,2014,75
11,2014 - Q2,Q2,2014,25
7,2014 - Q3,Q3,2014,200
10,2014 - Q3,Q3,2014,100
8,2014 - Q4,Q4,2014,175
9,2014 - Q4,Q4,2014,125
];
hth