topic YTD Calculation in a Straight Table in QlikView

YTD Calculation in a Straight Table

Fri, 04 Dec 2009 03:38:12 GMT

I am trying to calculate the YTD amount in a straight table where my time dimension is the rows (see table below). I cannot use any of the "Accumulation" methods due to issues with combining calculations on data that does not require accumulation (Balance Sheet vs Income Statement Accounts).

I don't want to use a date selection because it needs to calculate for every date in the table. Any time I use a set analysis on my Period field (i.e. Period = {'>= yearstart(Period)'} ) I just get the calculation for the current month and not a YTD number. I can only find examples where the date is not a dimension and/or someone is selecting the date. I can do that, I just can't find the right code for this.

Convert column 'B' below to Column 'C'.

Thanks in advance for your help,

Period	Current Month	YTD Calc (How in QV?)	NET INCREASE IN CASH	Prev YTD	Ending Cash Calc
A	B	C	D	E	F
Jan08	(8,518,694)	-8,518,694	4,470,938	0	0
Feb08	207,532	-8,311,161	-207,532	8,518,694	8,311,161
Mar08	701,159	-7,610,002	-701,159	8,311,161	7,402,470
Apr08	(262,333)	-7,872,336	262,333	7,610,002	6,963,644
May08	(402,577)	-8,274,913	402,577	7,872,336	7,628,555
Jun08	(272,945)	-8,547,858	274,632	8,274,913	8,305,764
Jul08	(172,186)	-8,720,043	172,186	8,547,858	8,750,895
Aug08	44,980	-8,675,064	-44,378	8,720,043	8,878,702
Sep08	(728,669)	-9,403,733	728,669	8,675,064	9,562,392
Oct08	457,146	-8,946,587	-457,146	9,403,733	9,833,915
Nov08	1,254,003	-7,692,584	-1,261,727	8,946,587	8,115,042
Dec08	757,008	-6,935,576	-727,146	7,692,584	6,133,893
Jan09	231,979	-6,703,597	3,444,275	6,935,576	8,821,160
Feb09	(6,008)	-6,709,605	-196,461	6,703,597	8,600,253
Mar09	129,475	-6,580,129	-830,634	6,709,605	8,476,785
Apr09	442,578	-6,137,551	-182,201	6,580,129	7,902,775
May09	831,617	-5,305,935	-351,306	6,137,551	6,706,315
Jun09	455,698	-4,850,237	-180,382	5,305,935	5,419,684
Jul09	110,261	-4,739,976	64,693	4,850,237	4,856,493
Aug09	214,921	-4,525,055	-259,901	4,739,976	4,530,710
Sep09	257,189	-4,267,866	473,935	4,525,055	4,061,055
Oct09	(457,146)	-4,725,011	0	4,267,866	4,261,011
Nov09	(1,254,003)	-5,979,014	0	4,725,011	5,979,884
Dec09	(757,008)	-6,736,023	0	5,979,014	7,961,033

YTD Calculation in a Straight Table

Fri, 04 Dec 2009 03:58:58 GMT

Can you use Above()?

If(Above([Current Month]), Above([YTD Calc]) + [Current Month], [Current Month])

This expression is called YTD Calc, if you want to use a different label, you need to change Above([YTD Calc]) in the expression to reflect your YTD expression label.

YTD Calculation in a Straight Table

Fri, 04 Dec 2009 22:02:38 GMT

I tried the Above() function and it just gives me the sum of the previous month plus the current month. It does not roll forward. In my table, the [YTD Calc] column is the formula that I need, so I do not have anything to substitute in your example. That is the missing formula I can't seem to figure out without using the "Accumulation" function.

YTD Calculation in a Straight Table

Sat, 05 Dec 2009 03:42:34 GMT

I figured it out. After much searching and trying various topics, I used a combination of the rangesum and above functions. Here is what I got to work:

=Rangesum(Above(sum([Actual]),0,Num(Month(Period))))

where the above function allows you to choose how many row backs to start - in this case zero (0). Then I use my Period to tell it how many rows back to go (Num(Month(Period)). This way, when you get to a new year, it will automatically only go back one month and not carry a balance forward from last year (since Num(Month(Period)) = 1).

This will overcome any issues with the "Full Accumulation" options in a table when you have multiple years.

Thanks for the help.

YTD Calculation in a Straight Table

Sun, 31 Jan 2010 19:38:54 GMT

Hello to all,

I have the same problem with YTD calculations. The solution proposed by Kevin works fine only when month is the last (or the only) level in the chart. In my case below Month there is Week and Day Level. When Week&Day are collapsed everything is OK. When I expand a month to go to the week level the function returns the sum([Actual]).

I cannot believe that is so difficult to do a simple multivelel YTD in qlik.

YTD Calculation in a Straight Table

Sun, 31 Jan 2010 22:01:47 GMT

I found a workaround to this problem by using dimensionality() function thanks to http://community.qlik.com/forums/t/25153.aspx.

I have a pivot table with Market-Month-Week-Day dimensions. When everything is collapsed dimensionality() returns 1. When Market is expanded to Months returns 2 e.t.c

so the solution is:


if (dimensionality() = 2, //MONTH LEVEL
 rangesum(above(sum(CY_AMOUNT),0,MONTH)),
 if(dimensionality() = 3, //WEEK LEVEL
 rangesum(above(sum(CY_AMOUNT),0,WEEK)),
 if(dimensionality() = 4, //DAY LEVEL
 rangesum(above(sum(CY_AMOUNT),0,MONTH_DAY)),
 sum(CY_AMOUNT) //Market Level
 )
 )
)

I don't know what the performace penalty is but it works at least!

I am looking now to find a more elegant solution by using set analysis.