topic YTD calculation based on user selection in QlikView

YTD calculation based on user selection

Tue, 21 Apr 2009 02:23:17 GMT

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);

YTD calculation based on user selection

Tue, 21 Apr 2009 02:53:14 GMT

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

YTD calculation based on user selection

Fri, 24 Apr 2009 03:42:57 GMT

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.

YTD calculation based on user selection

yblake — Fri, 24 Apr 2009 16:45:11 GMT

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;