2 periods

[ninnartx]

Hi everyone.

I have a requirement where invoice date might not be the same as the company's calendar dates.

For example, 30th September would fall under September in the normal calendar but the cut off date for September might have been the 28th, so the sales values from 30th September will actually fall under October on the company's calendar.

What's the best approach to this type of data? Would having 2 period tables, each linking to their respective date type in the fact table, be practical? Or should there only be 1 date on the fact table and then we have a different field in the period table indicating the company's calendar dates?

As you can see, I'm a little lost on how to approach this scenario. Any advise would be appreciated! Thank you!!

[Anonymous]

Hi, Ninnart,

You can just added a SalesYear and SalesMonth in the MasterCalendar as below, then use the SalesYear and SalesMonth as dimension in the table. And at the same time, still keep a normal calendar for other calculation.

Script for the MasterCalendar:

MinMax_temp:

LOAD

  Min(Date) as MinDate,

  Max(Date) as MaxDate

RESIDENT table_with_Date;

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax_PDT_temp'));

LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax_PDT_temp'));

Cal_temp:

LOAD

  Date($(vMinDate) + rowno() -1) as TempDate AutoGenerate $(vMaxDate) - $(vMinDate) + 1;

DROP TABLE MinMax_temp;

LOAD

    TempDate as Date,

    Year(TempDate) as Year,

    Month(TempDate) as Month,

    If (Month(TempDate)=12 and Day(TempDate)>28, Year(TempDate)+1, Year(Template) ) as SalesYear,

    If(Day(TempDate>28, if(Month(TempDate)=12, 1, Month(TempDate)+1), Month(TempDate))as SalesMonth,

  ....

Resident Cal_temp;

Zhihong

[ninnartx]

Hi Zhihong,

I don't think that would work since the cut off date isn't the same in each month. What ended up happening was that we decided to map the cut off date in SQL instead. But thank you so much for your help!!!

[Anonymous]

Hi, Ninnart,

As there are only a fixed 12 months for a year, then you need 12 or less "if" cases in the script to process each month, and if the cut off date for each month is dynamic, then there should have some rule for that.

I think the date mapping/shift in SQL is the same as do it in Qlikview, But any way, if you solve it in SQL, that is OK.

But then you lose the connection of the date to be used in Sales report (which use shift date), and the normal company report in you need to combine them together. However, if you need Sales report and company report in different dash, then it dose not matter.

Zhihong