Help me to build Master Calender by useing period

[venkatasuresh]

Hi Guys,

I have Field called 'Period'

In that I have period 1-12 like below,

Load *Inline[Period

1

2

3

4

5

6

7

8

9

10

11

12];

Based on this I have to create four Quarters can you any give me suggestions how to prepare Quarters

Note: I don't have Day&year information but the report contains only one year data 2019

-We are maintaining separate reports for individual years because huge valume of data.

[venkatasuresh · Accepted solution]

Hi Guys,

I got the solution.

Source:
Load * Inline [ Month
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12
];

Temp:

Load

min(makedate(2019,Month,1)) as minDate,

max(makedate(2019,Month,31)) as maxDate

Resident Source;
Drop Table Source;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

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

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate AS OrderDate,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,
'Q'&Ceil(Num(Month(AddMonths(TempDate,0)))/3) as Quarter

Resident TempCalendar

Order By TempDate ASC;