topic Re: YTD in Cyclic Group! in QlikView

YTD in Cyclic Group!

Sat, 25 Jun 2011 13:55:30 GMT

I have created a cyclic group called "Date" which contains fields such as Year, Month and Week. Along with these fields I want to create one more field which calculates "YTD". So that user can select YTD from the cyclic group like the way they select Year, Month and Week.

Any idea how to do that? Sample application will do good.

Thanks

Attitude

YTD in Cyclic Group!

nagaiank — Sat, 25 Jun 2011 14:23:10 GMT

If YTD is a field in any of your logical files loaded in the script, you can add this to the cyclic group.

If YTD is a field calculated by a chart expression, you cannot add the calculated field to the group

Re: YTD in Cyclic Group!

Sat, 25 Jun 2011 14:52:12 GMT

Hi Krishnamoorthy,

Makes sense but may I know how it can be calculated in the script so that I can use it in the cyclic group.

Temp_date:
Load Max(Floor(Year)) AS YTD resident Calendar;
Drop table Temp_date; //no longer needed 
LET vCurrentYear = Peek('YTD', 0, 'Temp_date');

After that can I add YTD in cyclic group as it is YTD is more less equal to start of the year to to till date and I guess Max(Floor(Year)) gives the same YTD. i,e Starting of the year to till date.

Please explain your view!

Thanks

Attitude

YTD in Cyclic Group!

Sat, 25 Jun 2011 15:07:22 GMT

Hi Attitude,

in addition to what krishnamoorthy said above: try this in your script (where you created Year, Month and Week):

LOAD ... . .

YearToDate( SourceDate) AS YTD,

. . .

HtH

Roland

YTD in Cyclic Group!

nagaiank — Sat, 25 Jun 2011 19:23:33 GMT

If the YTD value has to be calculated in the script, The 'To-Date' in YTD has to be defined prior to script execution.

One way is to default it to today's date. In this case, the reload schedule of the document has to be at least daily or as frequent as the data reload, if the relead schedule is more frequent than once per day. The YTD value calculated this way will be for the total set {1} as the user selection takes place after the script execution.

(One may try to use the variables in the script which can be set in the command-line of releoad. In this case, the policy for using a To-date in all YTD has to be acceptable to users.)

In all such techniques, we give up the user-interaction effects. I am not sure if adding YTD in a cyclic group will justify the effort of implementation or the functionality we forgo, especially when YTD/QTD/MTD analyses in comparison with previous YTD/QTD/MTD values can be done very elegantly using set analysis.

However everything depends on what the user wants, though a QlikView consultant can influence/manage user expectations.

Just my 2 cents.

Re: YTD in Cyclic Group!

Mon, 27 Jun 2011 07:28:05 GMT

Hi Roland

When I see example in the help I think it just returns true are false. Are you sure that it is going to work?

Examples:
Assume last reload time = 1999-11-18
yeartodate( '1998-11-18' ) returns false
yeartodate( '1999-02-01' ) returns true
yeartodate( '1999-11-18' ) returns true
yeartodate( '1999-11-19' ) returns false
yeartodate( '1998-11-18', -1 ) returns true
yeartodate( '1999-11-18', -1 ) returns false
yeartodate( '1999-04-30', 0, 5 ) returns false
yeartodate( '1999-05-01', 0, 5 ) returns true

Thanks

Attitude

Re: YTD in Cyclic Group!

Mon, 27 Jun 2011 07:37:11 GMT

Hi Krishnamorthy

Can you please provide some code? It will be helpful for me. Still I couldn't complete YTD Task.

Thanks

Attitude

Re: YTD in Cyclic Group!

Mon, 27 Jun 2011 07:50:43 GMT

Hi Attitude,

yes of course. Take a look at my little example.

Re: YTD in Cyclic Group!

Mon, 27 Jun 2011 11:14:57 GMT

Hi Roland

Thanks for your response. If I want the year to start from 27/12/YYYY instead of 01/01/YYYY. What changes will be required in the script.

Yet to implement your code in my application. Will try and let you know on that.

Thanks

Attitude

Re: YTD in Cyclic Group!

Mon, 27 Jun 2011 11:50:59 GMT

Hi Roland

What code I need to add in the below code to select the 4 days back from the current YTD. i,e MyDate should display date from 27/12/2010 to till date.Also let me know whether I am going in the right way.

=if(Year2date(MyDate)=-1 OR ????,MyDate)

Thanks

Attitude

Re: YTD in Cyclic Group!

Mon, 27 Jun 2011 12:20:02 GMT

Hi again,

this should work:

sum(if(YTD= -1 OR MyDate >= '27.12.2010' and MyDate < '01.01.2011',MyVal)) // -1 --> true

If you need it more often, put it into the script and create the values there.

Or at least use a variable similar to this:

MyYTD= "YTD= -1 OR MyDate >= '27.12.2010' and MyDate < '01.01.2011'"

And then in the expression sth like this:

sum(if( $(MyYTD), MyVal))

yeartodate( '1998-11-18' )	returns false
yeartodate( '1999-02-01' )	returns true
yeartodate( '1999-11-18' )	returns true
yeartodate( '1999-11-19' )	returns false
yeartodate( '1998-11-18', -1 )	returns true
yeartodate( '1999-11-18', -1 )	returns false
yeartodate( '1999-04-30', 0, 5 )	returns false
yeartodate( '1999-05-01', 0, 5 )	returns true