YTD calculation based on user selection

[#1]

How to calculate YTD based on user selection. If user selects period 010.2009 it is just displaying that period data. My time line sample code is as follows & YTD calculations is sum(cost * TTL_YTD)

monthstart(addmonths(COBK.BUDAT,9)) as PostingPeriod

PostingPeriod as TL_Date,

MINMAX:

LOAD min(TL_Date) as MinDate,

max(TL_Date) as MaxDate

RESIDENT LINKTAB

GROUP BY 1;

LET MinDate = peek('MinDate');

LET MaxDate = peek('MaxDate');

LET NoDays = MaxDate - MinDate + 1;

DROP TABLE MINMAX;

LOAD *,

num(Month(TL_Date)) as TL_FiscPeriod,

year(TL_Date) as TL_FiscYear,

inyeartodate(TL_Date, '$(MaxDate)', 0, 1) * -1 as TL_YTD,

addmonths(TL_Date, -9) as TL_CalDate;

LOAD date('$(MaxDate)' - recno() + 1) as TL_Date AUTOGENERATE $(NoDays);

[#2 · Accepted solution]

I don't have much time to look at your code right now. But here is a thread that may help you get started.

http://community.qlik.com/forums/t/15820.aspx

[#3]

The version being used is 8.2 so cannot use set analysis.Should the YTD amount be calculated in the script? Could you provide sample code.

[yblake]

Check forum for Qlikview cookbook provided by Rob Wunderlich. He shows a smart technique for user date range selection :

/* This section just creates some test data */
data:
LET vEndDate = num(today(1)); // End at today's date
LET vStartDate = num(addmonths(today(1),-14)) -1; // Start at 14 months earlier than today
TestData:
LOAD date($(vStartDate) + IterNo()) as Date
AUTOGENERATE 1
WHILE $(vStartDate) + IterNo() <= $(vEndDate)
;
/* End of test data */

/* This script demonstrates assigning Dates to named groups, such as "Yesterday" or "Last Week".
Date table was created on the previous tab.
*/

// Assign current date to a variable. Makes INLINE below a little neater and ensures we don't cross midnight boundry.
LET vToday=today(1);

Ranges:
// Use the "evaluate()" function to execute the input function text and convert it to actual date values.
LOAD Range, date(evaluate(RangeStart)) as RangeStart, date(evaluate(RangeEnd)) as RangeEnd
;
/* The INLINE data defines the Range Name, Starting date of the Range, and Ending date of the Range.
The Start/End values are defined using standard QV date functions.
Semicolon is used for a field delimeter because some of the functions contain commas.
*/
LOAD * INLINE [
Range; RangeStart; RangeEnd
Today; vToday; DayEnd(vToday)
Yesterday; vToday-1; DayEnd(vToday-1)
This Week; WeekStart(vToday); WeekEnd(vToday)
Last Week; WeekStart(vToday,-1); WeekEnd(vToday, -1)
Last 2 Weeks; WeekStart(today(1),-1); WeekEnd(today(1))
This Month; MonthStart(vToday); MonthEnd(vToday)
Last Month; MonthStart(vToday,-1); MonthEnd(vToday,-1)
Last Two Months; MonthStart(vToday,-1); MonthEnd(vToday)
This Year; YearStart(vToday); YearEnd(vToday)
Last Year; YearStart(vToday,-1); YearEnd(vToday,-1)
] (delimiter is ';')
;

/*
Use IntervalMatch to link the Date field into multiple Range fields.
Doing a JOIN avoids creating a messy synthetic key.
*/
JOIN (Ranges) IntervalMatch (Date) LOAD RangeStart, RangeEnd RESIDENT Ranges;
// Because we did a JOIN, we may drop the the Start/End fields.
DROP FIELDS RangeStart, RangeEnd;