Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

YTD in Cyclic Group!

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

10 Replies
nagaiank
Valued Contributor III

YTD in Cyclic Group!

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

Not applicable

Re: YTD in Cyclic Group!

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

Not applicable

YTD in Cyclic Group!

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
Valued Contributor III

YTD in Cyclic Group!

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.

Not applicable

Re: YTD in Cyclic Group!

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

Not applicable

Re: YTD in Cyclic Group!

Hi Krishnamorthy

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

Thanks

Attitude

Not applicable

Re: YTD in Cyclic Group!

Hi Attitude,

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

RR

Not applicable

Re: YTD in Cyclic Group!

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

Not applicable

Re: YTD in Cyclic Group!

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