Inputs need to create a master calendar (fiscal)

[#1]

Dear Qlik Gurus,

Hope you are doing well.

Iam required to create a fiscal calendar but not having date field(day date) in my tables. Available fields are as below:

1.MonthName i.e January,February,March etc

2.MonthId i.e 1,2,3,4,5,....12

3.FYName i.e fiscal year name FY15, FY16 etc

4.FYId i.e fiscal year id 1,2,3 etc ( '1' for 2016,    '2' for 2015 fiscal year ids etc)

5.FiscalYearStart  values are 2015,2014 (i.e 2015 for FY16,2014 for FY15 etc)

6.YearDesc values are 2016,2015 etc.

Table1
MonthId
MonthName

Table2
FYId
FiscalYearStart
YearDesc
FYName

Fact Table
FYId
MonthId

how can we  generate fiscal calendar i.e July start from above fields i.e month name,fiscal year names?

Please let me know if any information needed.

Thanks in advance.

Regards,

Kishore.

[krishnakumars7]

You can use makedate function MAKEDATE(YearDesc,MonthId,'01')

[adamdavi3s]

Does this help as a starter?

[#4]

Hi Adam,

Thanks very much for response. Iam not able to view as i do not have license, is it possible to share script?

kishorekumarkarakavalasa@gmail.com(if you dont have any prob)

Thanks and Regards,

Kishore.

[adamdavi3s]

Table2:

LOAD * INLINE [

FYId,FiscalYearStart,YearDesc,FYNam

1,2015,2016,FY16

2,2014,2015,FY15

];

FACT:

LOAD * INLINE [

    $fykey,FYId, MonthId, data

    1-7,1, 7, 1

    1-8,1, 8, 2

    1-9,1, 9, 3

    1-10,1, 10, 4

    1-11,1, 11, 5

    1-12,1, 12, 6

    2-1,2, 1, 7

    2-2,2, 2, 8

    2-3,2, 3, 9

    2-4,2, 4, 10

    2-5,2, 5, 11

    2-6,2, 6, 12

];

FISCAL_CAL:

load $fykey,

  FYId as cal_FYId,

  MonthId as cal_MonthID,

  if(MonthId >=7, MonthId -6, MonthId +6) as FY_Month

Resident FACT;

LEFT JOIN

LOAD FYId as cal_FYId,FiscalYearStart,YearDesc,FYNam

Resident Table2;

drop table Table2;

[adamdavi3s]

What is the point in this when the date won't link to any data?

This is creating a level of granularity not needed in this case. That is of course assuming that the details provided above are correct

[#7]

Adam,appreciate your work. really helpful.

One doubt: what if we have to do the same for more than 2 years i.e 2015,2016,2017,2018 etc

Our data gets loaded every month(12 times in a year).

Do we have to update every time in the inline load?

for clarification,can you show Fact table for 3 years.

Our fact table includes more fields along with the above.

Basic coverage is Monthly wise data to be loaded for multiple years , it can be one or more than 2 years.

Thanks again for response.

Regards,

Kishore.

[adamdavi3s]

Hi Kishore,

To be honest that was just an example.

I would probably build it all on the fly from the fact table to be sure that you have all the data you need, there will be more efficient ways of doing this probably

So create the key in your FACT then just build it all up using standard code rather than storing everything in the table2

something like (and i've just written this here and not tested the syntax) this but you can tinker with it to get what you need

FISCAL_CAL:

load $fykey,
  FYId as cal_FYId,
  MonthId as cal_MonthID,
  if(MonthId >=7, MonthId -6, MonthId +6) as FY_Month,
  if(num(month(now()))<=12 and num(month(now()))>=7, Year(now()) , year(now()) -1 ) as FiscalYearStart,
if(num(month(now()))<=12 and num(month(now()))>=7, 'CY'&right(Year(now()),2) ,  'CY'&right(Year(now())-1,2) ) as FYName 
Resident FACT;

[#9]

Thanks Adam, will work on it.

[adamdavi3s · Accepted solution]

Not a problem and if you have any questions please don't hesitate to ask.

Please do mark a correct or helpful answer though, if you feel I've been helpful of course!