Count of quarter.

[kavita25]

Hello all,

I have data of 4 year so I want to show data quarter wise .

For.eg 

In 4 year there are 16 quarters , I want quarters in this format shown as below :

Q1, Q2,Q3,Q4,Q5.....Q15,Q16 etc

Can anyone help me to get quarter in that format I have date field as in  DD-MM-YYYY.

Highly appreciate for any solution.

Regards,

Kavita 

[Taoufiq_Zarra]

@kavita25 

May be this version :

First, I create a map to extract  quarters
vMindate you can define it directly (Let) as you can extract it from the script with a peek, the idea is to save this date.

I generated these datas to show what I mean.

Script :

let vMnDate=2016;

QuartersMap:

MAPPING LOAD 

rowno() as Month,

Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Data:


LOAD *,'Q '&(ApplyMap('QuartersMap', month(TempDate), Null())+4*(Year(TempDate)-$(vMnYear))) as Quarter INLINE [
	ID,TempDate,Sales
    1, 01/01/2016, 10
    2, 02/01/2016, 20
    3, 03/01/2016, 30
    4, 04/01/2016, 40
    5, 05/01/2016, 50
    6, 06/01/2016, 60
    7, 07/01/2016, 70
    8, 08/01/2016, 80
    9, 09/01/2016, 90
    10, 10/01/2016, 100
......
]

output:

1-

attached qvf file

[kavita25]

hello  @Taoufiq_Zarra  Specialist II

,

Thanks for your solution ,  but i want  other way around

for eg:

2016 will be Q16 

2019 will be Q1,Q2....Etc 

and i want this fiscal year wise .

Regards,

Kavita

[Taoufiq_Zarra · Accepted solution]

Hi @kavita25 

A really good challenge !

Assume that Fiscal Month start at Month=4

this is the new script :

SET vFiscalYearStartMonth = 4;

Data:


LOAD * INLINE [
	ID,TempDate,Sales
    1, 01/01/2016, 10
    2, 02/01/2016, 20
    3, 03/01/2016, 30
    4, 04/01/2016, 40
    5, 05/01/2016, 50
    6, 06/01/2016, 60
    7, 07/01/2016, 70
    8, 08/01/2016, 80
    9, 09/01/2016, 90
    10, 10/01/2016, 100
    11, 11/01/2016, 110
    12, 12/01/2016, 120
    13, 13/01/2016, 130
    14, 14/01/2016, 140
    15, 15/01/2016, 150
    16, 16/01/2016, 160
    17, 17/01/2016, 170
    18, 18/01/2016, 180
    19, 19/01/2016, 190
    20, 20/01/2016, 200
    21, 21/01/2016, 210
    22, 22/01/2016, 220
    23, 23/01/2016, 230
    24, 24/01/2016, 240
...
];

Temp:

Load

               min(TempDate) as minDate,

               max(TempDate) as maxDate

Resident Data;

Let varMinDate = Date(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Date(Peek('maxDate', 0, 'Temp'));

LET vStartDate = Num(YearStart('$(varMinDate)'));

LET vEndDate = Num(YearEnd('$(varMaxDate)'));

LET MaxYearQ = Num(Year('$(varMaxDate)'));

drop table Temp;

FiscalCalendar:

LOAD

*,

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter
Dual('Q' & (Num(Ceil(FiscalMonth/3))+4*($(MaxYearQ)-Year(TempDate))), (Num(Ceil(FiscalMonth/3))+4*($(MaxYearQ)-Year(TempDate)))) AS NewFiscalQuarter, // NewFiscal Calendar Quarter
//'Q '&(ApplyMap('QuartersMap', month(TempDate), Null())+4*($(MaxYear)-Year(TempDate))) as NewFiscalQuarter,
Dual(Text(Date(MonthEnd(TempDate), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

*,

Year(TempDate) AS Year, // Standard Calendar Year

Month(TempDate) AS Month, // Standard Calendar Month

Date(MonthEnd(TempDate), 'MMM') AS MonthName,  // Standard Calendar Month Name

Dual('Q' & Ceil(Month(TempDate)/3), Ceil(Month(TempDate)/3)) AS Quarter,  // Standard Calendar Quarter

Mod(Month(TempDate) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal Calendar Month

YearName(TempDate, 0, $(vFiscalYearStartMonth)) AS FiscalYear;  // Fiscal Calendar Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS TempDate,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

more info about FiscalCalendar here

output :

you've all the Quarters:

         -Regular Q1->Q4
         -Fiscal Q1->Q4
         -New Fiscal Q1->Q16

attached qvf file

[Dharanidharan_DD]

Hi,

Can u help me to create like this , Its a quarter Number till now.

63
63
63
60
60
60
57
57
57
54
54
54
51
51
51
48
48
48
45
45
45
42
42
42
39
39
39
36
36
36
33
33
33
30
30
30
27
27
27
24
24
24
21
21
21
18
18
18
15
15
15
12
12
12
9
9
9
6
6
6
4
4
4