Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
ninnartx
Creator
Creator

2 periods

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!!

3 Replies
geminihzh
Specialist
Specialist

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
Creator
Creator
Author

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!!!

geminihzh
Specialist
Specialist

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