YTD Expressions

Mon, 08 Feb 2010 21:42:57 GMT

I am new to Qlikview and would like to create a YTD calculation using an expression. The calendar information we use contains a Year and Period combination. All help is welcome...Thanks!

YTD Expressions

disqr_rm — Mon, 08 Feb 2010 22:18:41 GMT

Try this in a new QV and you will get an idea:

Dates:
LOAD *,
month(Date) as Month,
year(Date) as Year,
week(Date) as Week,
inyeartodate(Date, today(), 0) * -1 as YTDFlag,
inyeartodate(Date, today(), -1) * -1 as PrevYTDFlag;
LOAD Date(today() - recno()) as Date
AUTOGENERATE 500;

YTD Expressions

johnw — Mon, 08 Feb 2010 22:19:17 GMT

The right approach depends a lot on the specifics.

If, say, you want a chart of current year data, showing both the month and the year to date for that month, you can simply turn on "full accumulation" on the expressions tab.

If you want the user to select one and only one month or date, you can use set analysis to "open up" your date range to include the year to date. Something like sum({<Date={">=$(=yearstart(Date)) <=$(=Date)"}>} Sales).

If you want a chart of dates that could include multiple years, you can create a "date island", a list of all dates that is disconnected from your main data model, use it as the dimension, then connect those dates to your real dates in the expression. Something like sum(if(Date>=yearstart(DateIslandDate) and Date <=DateIslandDate,Sales)). It's a simple solution for small data sets, but can bog down on big data sets.

For a big data set, you could create a linkage table that connects your records to dates via a "date type". So you can have 'Current' and 'YTD' date types. For a given date and the 'Current' date type, you connect it only to records that happened on that exact date. For a given date and the 'YTD' date type, you connect it to all records with dates in the year to date. Then your expression just has to specify the date type. Something like sum({<DateType={'YTD'}>} Sales). That should perform better than the date island approach on large data sets, but is more complicatd to set up, and takes more memory.

Probably other approaches as well. We'll need more details if you need more details.

topic YTD Expressions in QlikView

YTD Expressions

YTD Expressions

YTD Expressions