Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
hi
please find the attachment
but i am not able to understand what is your fiscal year
thanks
rohit
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
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
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
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
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
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
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