Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

tracycrown
Contributor II

Fiscal Calendar

Dear all

I am new to QV, can someone help me why YTD column is not working correctly in Q4-2011 ?.

Thank you very much

Tracy

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Fiscal Calendar

Hi Tracy,

I've noticed that your source table don't inlcude a full financial year, since the dates are the beginning are from year 2000, then there is a big gap until Jan 2011. I assume that this is by accident and I've modified the table and added some more data, just to demonstrate (It will also work with your original data, but then you can't see any data for the FY months, of course).

Please note that I added my suggested two expressions to your table load script, they seem to work just fine. In general, I would suggest to use a master calendar which has an date entry for each day, regardless if any Amount is linked to that date. This makes it possible to select those dates (you will notice that there are some months, e.g. in FY 2011, that if you you select on, will not show a correct FYtD value, this is because here we have missing values in the date, resp YearMonth fields. A master calendar would fix that. There are some examples available here in the forum, just search for master calendar).

I then replaced the year list box in the UI with FY and used this as expression in your table:

=num(sum({$<FY = {$(=max(FY))}, Year=,Quarter=, YearMonth = {"<=$(=max(YearMonth))"},Month= >} Amount), '$#,##0;($#,##0)')

So, I set FY to the max possible FY value, and limit YearMonth to YearMonth smaller or equal the max possible value. All other date fields the user may select on need to be cleared.

Hope this helps,

Stefan

16 Replies
MVP
MVP

Re: Fiscal Calendar

I believe your problem is that you use something like num(month(Date)) as Month. Thus you create a numerical range 1 to 12 for Month, for all years. If you need to cross calendar year bounderies, e.g. if you need to some fiscal year to date, your set expression Month = {"<=$(=max({<Year={$(=max(Year))}>} Month)) will - in the example given- result only in the set of Month values {1,2,3}, the month {4,5,6,7,8,9,10,11,12} of the previous calendar year are missing.

I suggest that you use a numerical value here for month, which is contiguous across year bounderies, I think you could use monthstart(Date), and if you want to format it like Jan / Feb etc,  maybe

Date(monthstart(Date),'YYYY-MMM') as YearMonth

Please take care that your Date is a recognized date type field.

You could also flag your Dates with an additional Fiscal year maybe using

Date(yearstart(Date,0,4),'YYYY') as FY

This makes it easier to limit your Dates in a FY then. You could do similar to get fiscal periods (P1 equals April etc.)

If you create more and more fields in your calendar, you probably find it helpful to build a master calendar and link your fact table to that. Search for master calendar, there are some examples here in the forum.

Hope this helps,

Stefan

rohit214
Contributor III

Re: Fiscal Calendar

hi

please find the attachment

but i  am not able to understand what is your fiscal year

thanks

rohit

Highlighted
tracycrown
Contributor II

Re: Fiscal Calendar

Hi Rohit

The fiscal calender should start from Apr 2010 (mistake in worsheet as 2000) to Mar 2011.

// Quarter, Month, MonthName

// Q1, 4, Apr

// Q1, 5, May

// Q1, 6, Jun

// Q2, 7, Jul

// Q2, 8, Aug

// Q2, 9, Sep

// Q3, 10, Oct

// Q3, 11, Nov

// Q3, 12, Dec

// Q4, 1, Jan

// Q4, 2, Feb

// Q4, 3, Mar

Thanks a lot

Tracy

Date: Mon, 21 Nov 2011 00:53:41 -0500

From: qliktech@sgaur.hosted.jivesoftware.com

To: tracycrown@hotmail.com

Subject: - Re: Fiscal Calendar

QlikCommunity

Re: Fiscal Calendar created by rohit214 in Development (QlikView Desktop) - View the full discussion

hi

please find the attachment

but i am not able to understand what is your fiscal year

thanks

rohit

Reply to this message by replying to this email -or- go to the message on QlikCommunity

Start a new discussion in Development (QlikView Desktop) by email or at QlikCommunity

© 1993-2011 QlikTech International AB Copyright & Trademarks | Privacy | Terms of Use | Software EULA

tracycrown
Contributor II

Re: Fiscal Calendar

Hi Stefan

I have tried to change the script according to your suggestion below but it does not work at all, you may amend the script and send back to me.

Date(monthstart(Date),'MMM') as Month

Date(yearstart(Date,0,4),'YYYY') as FY

Thanks a lot

Tracy

Date: Sun, 20 Nov 2011 11:54:37 -0500

From: qliktech@sgaur.hosted.jivesoftware.com

To: tracycrown@hotmail.com

Subject: - Re: Fiscal Calendar

QlikCommunity

Re: Fiscal Calendar created by swuehl in Development (QlikView Desktop) - View the full discussion

I believe your problem is that you use something like num(month(Date)) as Month. Thus you create a numerical range 1 to 12 for Month, for all years. If you need to cross calendar year bounderies, e.g. if you need to some fiscal year to date, your set expression Month = {"<=$(=max({<Year={$(=max(Year))}>} Month)) will - in the example given- result only in the set of Month values {1,2,3}.

I suggest that you use a numerical value here for month, which is contigouus across year bounderies, I think you could use monthstart(Date), and if you want to format it like Jan / Feb etc, maybe

Date(monthstart(Date),'MMM') as Month

Please take care that your Date is a recognized date type field.

You could also flag your Dates with an additional Fiscal year maybe using

Date(yearstart(Date,0,4),'YYYY') as FY

This makes it easier to limit your Dates in a FY then. You could do similar to get fiscal periods (P1 equals April etc.)

If you create more and more fields in your calendar, you probably find it helpful to build a master calendar and link your fact table to that. Search for master calendar, there are some examples here in the forum.

Hope this helps,

Stefan

Reply to this message by replying to this email -or- go to the message on QlikCommunity

Start a new discussion in Development (QlikView Desktop) by email or at QlikCommunity

© 1993-2011 QlikTech International AB Copyright & Trademarks | Privacy | Terms of Use | Software EULA

rohit214
Contributor III

Re: Fiscal Calendar

hi trcy

it won't show because fiscal year starts from April to march

but in your database date lies in Q3 .if you want to see data in Q4 then data should be

like 1/1/2012 to 31/3/2012 beacuse it will count in fiscal year of 2011 Q4

may it helps you to understand

what i want to say

thanks &best regard

rohit

MVP
MVP

Re: Fiscal Calendar

Hi Tracy,

I've noticed that your source table don't inlcude a full financial year, since the dates are the beginning are from year 2000, then there is a big gap until Jan 2011. I assume that this is by accident and I've modified the table and added some more data, just to demonstrate (It will also work with your original data, but then you can't see any data for the FY months, of course).

Please note that I added my suggested two expressions to your table load script, they seem to work just fine. In general, I would suggest to use a master calendar which has an date entry for each day, regardless if any Amount is linked to that date. This makes it possible to select those dates (you will notice that there are some months, e.g. in FY 2011, that if you you select on, will not show a correct FYtD value, this is because here we have missing values in the date, resp YearMonth fields. A master calendar would fix that. There are some examples available here in the forum, just search for master calendar).

I then replaced the year list box in the UI with FY and used this as expression in your table:

=num(sum({$<FY = {$(=max(FY))}, Year=,Quarter=, YearMonth = {"<=$(=max(YearMonth))"},Month= >} Amount), '$#,##0;($#,##0)')

So, I set FY to the max possible FY value, and limit YearMonth to YearMonth smaller or equal the max possible value. All other date fields the user may select on need to be cleared.

Hope this helps,

Stefan

tracycrown
Contributor II

Re: Fiscal Calendar

Dear Stefan

Thank you very much, YTD is working now.

I don't understand the meaning of the following script, please advise:

1. sum({$<FY = {$(=max(FY))},

2. Year=,

3. Quarter=,

4. YearMonth = {"<=$(=max(YearMonth))"},Month= >}

In addition, any good suggestion on QTD ?. I had tried YearQuarter but it does not work.

Thanks a lot

Tracy

tracycrown
Contributor II

Re: Fiscal Calendar

Dear Stefan

Thank you very much, YTD is working now.

I don't understand the meaning of the following script, please advise:

1. sum({$}

In addition, any good suggestion on QTD ?. I had tried YearQuarter but it does not work.

Thanks a lot

Tracy

Date: Wed, 23 Nov 2011 07:03:14 -0500

From: qliktech@sgaur.hosted.jivesoftware.com

To: tracycrown@hotmail.com

Subject: - Re: Fiscal Calendar

QlikCommunity

Re: Fiscal Calendar created by swuehl in Development (QlikView Desktop) - View the full discussion

Hi Tracy,

I've noticed that your source table don't inlcude a full financial year, since the dates are the beginning are from year 2000, then there is a big gap until Jan 2011. I assume that this is by accident and I've modified the table and added some more data, just to demonstrate (It will also work with your original data, but then you can't see any data for the FY months, of course).

Please note that I added my suggested two expressions to your table load script, they seem to work just fine. In general, I would suggest to use a master calendar which has an date entry for each day, regardless if any Amount is linked to that date. This makes it possible to select those dates (you will notice that there are some months, e.g. in FY 2011, that if you you select on, will not show a correct FYtD value, this is because here we have missing values in the date, resp YearMonth fields. A master calendar would fix that. There are some examples available here in the forum, just search for master calendar).

I then replaced the year list box in the UI with FY and used this as expression in your table:

=num(sum({$} Amount), '$#,##0;($#,##0)')

So, I set FY to the max possible FY value, and limit YearMonth to YearMonth smaller or equal the max possible value. All other date fields the user may select on need to be cleared.

Hope this helps,

Stefan

Reply to this message by replying to this email -or- go to the message on QlikCommunity

Start a new discussion in Development (QlikView Desktop) by email or at QlikCommunity

© 1993-2011 QlikTech International AB Copyright & Trademarks | Privacy | Terms of Use | Software EULA

MVP
MVP

Re: Fiscal Calendar

Tracy,

a QTD could look like:

=num(sum({$<FY = {$(=max(FY))}, Year=, Quarter=, QNum= {$(=max(QNum))} , YearMonth = {"<=$(=max(YearMonth))"}, Month= >} Amount), '$#,##0;($#,##0)')

Here I created a numerical Quarter field, so I can use Max() function on that (well, I could have also used a dual field value or maybe maxstring() function instead).

I don't understand the meaning of the following script, please advise:

1. sum({$<FY = {$(=max(FY))},

2. Year=,

3. Quarter=,

4. YearMonth = {"<=$(=max(YearMonth))"},Month= >}

A part in the set expression like Year= is telling QV to disregard any selection on that year.  FY= {$(=max(FY))} is explicitely setting the max possible value for our financial year FY to this field (so only one value is selected and used for calculation).

YearMonth = {"<=$(=max(YearMonth))"} is using a selection for YearMonth that includes any possible value lower or equal the current maximum possible value, so if you select e.g. 2011-10, any YearMonth from the beginning up to 2011-10 is being used, as far as not excluded by another selection, e.g. in field FY.

If you are not familiar with point in time calculations using set analysis, maybe you want to have a look into

http://iqlik.wordpress.com/2010/11/27/the-magic-of-set-analysis-point-in-time-reporting/

which is probably explaining things better than I do.

Regards,

Stefan