Extend incomplete month to 12 months

[uacg0009]

I don't know whether or not it is possible, just like the title said.

If I have some data and month is one of them. But for example, the month only has April, May, June, July and August.

And I want to make a list to show all 12 months, can I write some scripts to get that?

Thank you all!

[jagan · Accepted solution]

Hi,

Arrive a new Master Calendar by using the script below

CalendarMaster:

LOAD

    Date(InvoiceDate) AS InvoiceDate,

    Year(InvoiceDate) AS Year,

    'Q' & Ceil(Month(InvoiceDate) / 3) AS Quarter,   

    Month(InvoiceDate) As Month,

    Day(InvoiceDate) As Day,

    Week(InvoiceDate) As Week;

Load Date(MinDate + IterNo() -1 ) AS InvoiceDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

    YearStart(Min(DateDimensionName)) AS MinDate,

    YearEnd(Max(DateDimensionName)) AS MaxDate

RESIDENT TableName;

In the above script, replace 'TableName' with your table name and DateDimensionNamewith your date field.

Hope this helps you.

Regards,

Jagan.

[er_mohit]

Create a master calendar and then link to the date field which you used but format of date is same like

Write this code in script side for master calendar

LET vDateMin = Num(MakeDate(2006,1,1));

LET vDateMax = Floor(DayEnd(Today()));

TempCalendar:

LOAD

$(vDateMin) + RowNo() -1 AS DateNumber ,

Date($(vDateMin) + RowNo() -1) AS TempDate

//AUTOGENERATE $(vDateToday)-$(vDateMin);

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()<= $(vDateMax);

Calendar:

LOAD

num(Date(TempDate,'DD-MM-YYYY')) as linkDate,

Date(TempDate,'DD-MM-YYYY')as TempDate,

// Standard Date Objects

Day(TempDate) AS CalendarDayOfMonth,

//WeekDay(TempDate) AS CalendarDayName,

Month(TempDate) AS CalendarMonthName,

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

Year(TempDate) AS CalendarYear

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

load num(InvoiceDate) as linkdate

from datasource;

[uacg0009]

Hi Jagan, this is very useful for me and thank you very much.

But I have a problem, I think the script that you told me should execute this firstly:

Load

    YearStart(Min(DateDimensionName)) AS MinDate,

    YearEnd(Max(DateDimensionName)) AS MaxDate

RESIDENT TableName;

then according to the MinDate and MaxDate to calculate InvoiceData, so then it should execute this:

Load Date(MinDate + IterNo() -1 ) AS InvoiceDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

and finally it should execute:

CalendarMaster:

LOAD

    Date(InvoiceDate) AS InvoiceDate,

    Year(InvoiceDate) AS Year,

    'Q' & Ceil(Month(InvoiceDate) / 3) AS Quarter,  

    Month(InvoiceDate) As Month,

    Day(InvoiceDate) As Day,

    Week(InvoiceDate) As Week;

to calculate the month that is what I need. So I use this order to run my script, but I can't see CalendarMaster table,

I don't know why? Could you please clarify it for me? Thank you!

[jagan]

HI,

Can you attach some sample data.

Regards,

jagan.

[uacg0009]

just I have data like this:

Month_num

4

5

6

7

8

and the total script is :

Time:

LOAD

     Month_num

FROM

F:\mipo\BI\yuexiu\DATA_DEMO.xlsx

(ooxml, embedded labels, table is time1);

Load

    YearStart(Min(Month_num)) AS MinDate,

    YearEnd(Max(Month_num)) AS MaxDate

RESIDENT Time;

Load Date(MinDate + IterNo() -1 ) AS InvoiceDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

CalendarMaster:

LOAD

    Month(InvoiceDate) As Month;

Using this script order, I can't see the CalendarMaster table, but if I use this order below, I can see CalendarMaster table:

Time:

LOAD

     Month_num

FROM

F:\mipo\BI\yuexiu\DATA_DEMO.xlsx

(ooxml, embedded labels, table is time1);

CalendarMaster:

LOAD

    Month(InvoiceDate) As Month;

Load Date(MinDate + IterNo() -1 ) AS InvoiceDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

    YearStart(Min(Month_num)) AS MinDate,

    YearEnd(Max(Month_num)) AS MaxDate

RESIDENT Time;

[jagan]

Hi,

Check attached file for solution.  It has the following script

Time:

LOAD

MakeDate(Year(Today()), Month_num, 1) AS Month_num

INLINE [

    Month_num

4

5

6

7

8

];

Calendar:

LOAD

*,

Month(InvoiceDate) As Month;

Load

Date(MinDate + IterNo() -1 ) AS InvoiceDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

    YearStart(Min(Month_num)) AS MinDate,

    YearEnd(Max(Month_num)) AS MaxDate

RESIDENT Time;

Note: Don't split the scripts, copy and paste the script as such.  You splitted the script in above post.

Regads,

jagan.

[uacg0009]

thank you but I just want to know why the script order is

LOAD

*,

Month(InvoiceDate) As Month;

firstly and

Load

    YearStart(Min(Month_num)) AS MinDate,

    YearEnd(Max(Month_num)) AS MaxDate

RESIDENT Time;

finally.

Because I think if you want to get  InvoiceDate, you need to calculate from

Load

Date(MinDate + IterNo() -1 ) AS InvoiceDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

and if you want to get MinDate and MaxDate, you need to load month_num firstly like below:

Load

    YearStart(Min(Month_num)) AS MinDate,

    YearEnd(Max(Month_num)) AS MaxDate

RESIDENT Time;

So why the load Month_num script is not putting above of the two others scripts in script?

and when I put the Calendar script on the last, why I can't see it?

Thank you!

[jagan]

Hi,

This is called the Preceding load

Calendar:

LOAD

*,

Month(InvoiceDate) As Month;

Load

Date(MinDate + IterNo() -1 ) AS InvoiceDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

    YearStart(Min(Month_num)) AS MinDate,

    YearEnd(Max(Month_num)) AS MaxDate

RESIDENT Time;

First MinDate and MaxDate is retrieved and then using that InvoiceDate is generated and then Month is calculating.  All this are single statement.  That is why it is called preceding load.

Regards,

Jagan.

[uacg0009]

Thank you so much and I learnt a lot.

And I want to know what situation or special situation I need use Preceding load or Preceding load is all purpose?