Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

tracycrown
Contributor II

Re: Fiscal Calendar

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

Highlighted
MVP
MVP

Fiscal Calendar

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?

tracycrown
Contributor II

Re: Fiscal Calendar

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

MVP
MVP

Re: Fiscal Calendar

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

tracycrown
Contributor II

Re: Fiscal Calendar

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

MVP
MVP

Re: Fiscal Calendar

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

tracycrown
Contributor II

Re: Fiscal Calendar

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

Community Browser