Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
JustinDallas
Specialist III
Specialist III

Convert Fiscal Period to Date

Hello Folks,

I have a some data that only has Period and Year data.  I also would like to use a Master Calendar, but I don't know how to handle the Period issue.  I'm thinking about using the first day of a Period and linking it to the Calendar on that value, but I'm not sure how to calculate the first day in the period.

How do you reconcile Period based Account Summaries and Master Calendars?

1 Solution

Accepted Solutions
chriscammers
Partner - Specialist
Partner - Specialist

The first thing you have to do is figure out what month is the start of your year.

In your load script you need to establish some reference to the beginning month so let's say your fiscal year begins in July

set a variable to the beginning month number.

let vYearBeginMonth = 7;

then load your periods, the monthstart function has an offset parameter that allows you to change the begin date that will be returned so as long as we know when the year starts then you can adjust the periods to reflect the right date.

Load

Year, Period, Monthstart(MakeDate(Year,Period,1),($(vYearBeginMonth) - 12) - 1) as PeriodStartDate

Inline [

Year, Period

2016,1

2016,2

2016,3

2016,4

2016,5

2016,6

2016,7

2016,8

2016,9

2016,10

2016,11

2016,12

];

View solution in original post

4 Replies
laddu_927
Specialist
Specialist

Can you provide more details?

JustinDallas
Specialist III
Specialist III
Author

Given a table with these columns, how do you link it to a Master Calendar?

Period.PNG

laddu_927
Specialist
Specialist

does any of ACTINDX or PERIODID are anyhow related to dates or months?? If not then you cannot attain master calendar with granularity less than year.

chriscammers
Partner - Specialist
Partner - Specialist

The first thing you have to do is figure out what month is the start of your year.

In your load script you need to establish some reference to the beginning month so let's say your fiscal year begins in July

set a variable to the beginning month number.

let vYearBeginMonth = 7;

then load your periods, the monthstart function has an offset parameter that allows you to change the begin date that will be returned so as long as we know when the year starts then you can adjust the periods to reflect the right date.

Load

Year, Period, Monthstart(MakeDate(Year,Period,1),($(vYearBeginMonth) - 12) - 1) as PeriodStartDate

Inline [

Year, Period

2016,1

2016,2

2016,3

2016,4

2016,5

2016,6

2016,7

2016,8

2016,9

2016,10

2016,11

2016,12

];