MAT calculation with quarterly data

[nitesh2730]

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 

@sunny_talwar 

[ArnadoSandoval]

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

[nitesh2730]

below is the screenshot of fields 

[ArnadoSandoval · Accepted solution]

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,

[ArnadoSandoval]

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