
YTD Calculation in a Straight Table
Neil Miller Dec 3, 2009 10:58 PM (in response to khull)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
khull Dec 4, 2009 5:02 PM (in response to Neil Miller)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
khull Dec 4, 2009 10:42 PM (in response to Neil Miller)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
Angelos Vlisidis Jan 31, 2010 2:38 PM (in response to khull)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
Angelos Vlisidis Jan 31, 2010 5:01 PM (in response to Angelos Vlisidis)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 MarketMonthWeekDay 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.


