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
Dear Stefan
You are good, your recommendation on QTD is correct.
I have just tested that MTD, YTD and QTD will not work after creating a user selection called "Day (15, 28, 29, 30,31), please advise what should be changed.
Thank you very much
Tracy
Date: Thu, 24 Nov 2011 12:28:42 -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
Tracy,
a QTD could look like:
=num(sum({$}
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
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,
what do you mean with user selection called Day (15,28,29,30,31) ? Have you created an additional field using Day(Date) as Day or something like this?
Dear Stefan
Yes, I had created a Day field using day(Date) and all (MTD, QTD & YTD) fail to work properly, kindly advise what should be changed ?.
Thank you
Tracy
Date: Sat, 26 Nov 2011 11:17:03 -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
Tracy,
what do you mean with user selection called Day (15,28,29,30,31) ? Have you created an additional field using Day(Date) as Day or something like this?
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,
I added a small master calendar and changed the set expressions to be date limited instead of month limited. Please check the attached file. It is important to clear all date fields the user can select on that may interfere with our fields we use to limit the date range.
Regards,
Stefan
Dear Stefan
Well done, I am so happy to see that everything is working fine.
Kindly help to explain the following as I am new to Qlikview :
1. dual(date(Date,'MMM'),pick(month(Date),10,11,12,1,2,3,4,5,6,7,8,9)) as Month;
What is the purpose of dual(date) and why need to pick ?
2. Date(makedate(2010,01,01)+recno()-1,'DD/MM/YYYY') as Date
What is recno()-1 for ?
3. AutoGenerate 2*365+91;
Why 91 ?
Thank you very much
Tracy
Date: Sat, 26 Nov 2011 20:31:17 -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
Tracy,
I added a small master calendar and changed the set expressions to be date limited instead of month limited. Please check the attached file. It is important to clear all date fields the user can select that may interfere with our fields we use to limit the date range.
Regards,
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
1. dual(date(Date,'MMM'),pick(month(Date),10,11,12,1,2,3,4,5,6,7,8,9)) as Month;
What is the purpose of dual(date) and why need to pick ?
2. Date(makedate(2010,01,01)+recno()-1,'DD/MM/YYYY') as Date
What is recno()-1 for ?
3. AutoGenerate 2*365+91;
Why 91 ?
1.
Forced association of an arbitrary string representation s with a given number representation x.
Here we are forcing an association between e.g. month name Jan and 10, Feb and 11 etc. month() of a date in january date will return 1, but here I use pick to get the corresponding lookup value for our fiscal period (=10).
Maybe this all is a little confusing, I introduced this to get a list box sort order of your month values in fiscal period order, please check the list box in the UI. You will notice that the month list starts with Apr.
2.
recno() returns an integer for the number of the currently read row of the input table. The first record is number 1. Since I want my calendar to start with January 1st,2010, I need to subtract 1.
3.
Ok, that must be confusing. 2*365+91 will just create an abitray number of days for your calendar (2 years and 3 months in my example, 3 months to finish the fiscal year 2011).
Hope that clarified everything, have fun
Stefan
Dear Stefan
Appreciate all your kind effort and professional advice.
I know I have a lot to learn, will need more help from you next time.
Thank you very much
Tracy
Date: Mon, 28 Nov 2011 12:23:52 -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
1. dual(date(Date,'MMM'),pick(month(Date),10,11,12,1,2,3,4,5,6,7,8,9)) as Month;
What is the purpose of dual(date) and why need to pick ?
2. Date(makedate(2010,01,01)+recno()-1,'DD/MM/YYYY') as Date
What is recno()-1 for ?
3. AutoGenerate 2*365+91;
Why 91 ?
1.
dual( s , x )
Forced association of an arbitrary string representation s with a given number representation x.
Here we are forcing an association between e.g. month name Jan and 10, Feb and 11 etc. month() of a date in january date will return 1, but here I use pick to get the corresponding lookup value for our fiscal period (=10).
Maybe this all is a little confusing, I introduced this to get a list box sort order of your month values in fiscal period order, please check the list box in the UI. You will notice that the month list starts with Apr.
2.
recno() returns an integer for the number of the currently read row of the input table. The first record is number 1. Since I want my calendar to start with January 1st,2010, I need to subtract 1.
3.
Ok, that must be confusing. 2*365+91 will just create an abitray number of days for your calendar (2 years and 3 months in my example, 3 months to finish the fiscal year 2011).
Hope that clarified everything, have fun
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