Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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!
You can use makedate function MAKEDATE(YearDesc,MonthId,'01')
Does this help as a starter?
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.
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;
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
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.
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;
Thanks Adam, will work on it.
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!