Calculating YTD in Qlick View

[EL_MAR]

I want to calculate the YTD for every given year, as seen in the table.

I have the following formula for the max year. Does anybody have an idea how to adapt the formula so the YTD is calculated individualy for every single year. If possible I would like a formel that can be used for the whole table instead of having a formula for each year

=Num((Sum({<Valuationdate={"$(vYearEnd)"}>}[Adjusted NAV])-Sum({<Valuationdate={"$(vYearStart)"}>}[Adjusted NAV]))/Sum({<Valuationdate={"$(vYearStart)"}>}[Adjusted NAV]),'##,##%')

[TauseefKhan]

Hi @EL_MAR,

Check with this:

YTD:
Num((
Sum({<Year(Valuationdate) = $::Year(Valuationdate), Valuationdate={"<=$(=Max({<Year(Valuationdate) = $::Year(Valuationdate)>} Valuationdate))"}>} [Adjusted NAV]) -
Sum({<Year(Valuationdate) = $::Year(Valuationdate), Valuationdate = {"$(=Date(YearStart(Max(Valuationdate)), 'YYYY-MM-DD'))"}>} [Adjusted NAV])
) / Sum({<Year(Valuationdate) = $::Year(Valuationdate), Valuationdate = {"$(=Date(YearStart(Max(Valuationdate)), 'YYYY-MM-DD'))"}>} [Adjusted NAV]),
'##,##%')

******Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.******** 

[EL_MAR]

Hi,

the function is not working. It says there is an error...

[TauseefKhan]

Check this:

Num(
(Sum({<Year(Valuationdate) = $::Year,
Valuationdate = {"<=$(=Max({<Year(Valuationdate) = $::Year>} Valuationdate))"}>}
[Adjusted NAV])
- Sum({<Year(Valuationdate) = $::Year,
Valuationdate = {"=$(=YearStart(Max({<Year(Valuationdate) = $::Year>} Valuationdate)))"}>}
[Adjusted NAV]))
/ Sum({<Year(Valuationdate) = $::Year,
Valuationdate = {"=$(=YearStart(Max({<Year(Valuationdate) = $::Year>} Valuationdate)))"}>}
[Adjusted NAV]),
'##,##%')

[EL_MAR]

It still indicates that there is an errror. I copy and past it straight as it was written

[lennart_mo]

You could try defining the variables with dynamic values rather than one static value.

The value of vYearEnd could be defined as YearEnd(Valuationdate) i.e., so you can use your initial formula with this variable, except maybe you'll need to add an '=' in the $ expression to resolve the string.

=Num((Sum({<Valuationdate={"$(=vYearEnd)"}>}[Adjusted NAV])
-Sum({<Valuationdate={"$(=vYearStart)"}>}[Adjusted NAV]))
/Sum({<Valuationdate={"$(=vYearStart)"}>}[Adjusted NAV]),'##,##%')

[EL_MAR]

If I use the vYearEnd the formula is calculating for just the current year. 

Maybe my script helps to find a solution...

[EL_MAR]

YTD formula:

=Num((Sum({<Valuationdate={"$(vYearEnd)"}>}[Adjusted NAV])-Sum({<Valuationdate={"$(vYearStart)"}>}[Adjusted NAV]))/Sum({<Valuationdate={"$(vYearStart)"}>}[Adjusted NAV]),'##,##%')

I wanted to add the additional formulas that are in the main formula:

vYearStart: =Monthend(YearStart(Max(Valuationdate)))

vYearEnd: =Max(Valuationdate)

[marcus_sommer]

Such matching shouldn't be done within the UI else within the data-model in the calendar and this is there quite easy, for example:

if(daynumberofyear(Date) < daynumberofyear(today()), 1, 0) as YTD,
daynumberofyear(Date) as DayNumber

and then an expression might be look like:

sum({< YTD = {1}>} Value)

[juliano_arruda]

@EL_MAR , criando uma tabela simples e usando a dimensão de ANO, cria a medida RangeSum(Above(Sum([Valor]), 0, RowNo())).

Acredito que seja suficiente para trazer o acumulado ano a ano. Seria isso?