YTD in Cyclic Group!

[#1]

Hi

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

[nagaiank]

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

[#3]

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

[#4]

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

[nagaiank]

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.

[#6]

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

[#7]

Hi Krishnamorthy

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

Thanks

Attitude

[#8]

Hi Attitude,

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

RR

[#9]

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

[#10]

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