topic Re: Calculating YTD in Qlick View in QlikView

Calculating YTD in Qlick View

EL_MAR — Fri, 15 Nov 2024 21:00:35 GMT

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]),'##,##%')

Re: Calculating YTD in Qlick View

TauseefKhan — Tue, 04 Jun 2024 10:27:43 GMT

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.********

Re: Calculating YTD in Qlick View

EL_MAR — Mon, 10 Jun 2024 07:23:30 GMT

Hi,

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

Re: Calculating YTD in Qlick View

TauseefKhan — Mon, 10 Jun 2024 07:41:37 GMT

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]),
'##,##%')

Re: Calculating YTD in Qlick View

EL_MAR — Thu, 13 Jun 2024 14:01:03 GMT

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

Re: Calculating YTD in Qlick View

lennart_mo — Thu, 13 Jun 2024 15:20:46 GMT

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]),'##,##%')

Re: Calculating YTD in Qlick View

EL_MAR — Fri, 14 Jun 2024 08:19:37 GMT

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

Maybe my script helps to find a solution...

Re: Calculating YTD in Qlick View

EL_MAR — Fri, 14 Jun 2024 14:29:01 GMT

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)

Re: Calculating YTD in Qlick View

marcus_sommer — Fri, 14 Jun 2024 16:53:54 GMT

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)

Re: Calculating YTD in Qlick View

juliano_arruda — Fri, 14 Jun 2024 18:32:58 GMT

@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?

Re: Calculating YTD in Qlick View

EL_MAR — Mon, 17 Jun 2024 14:37:22 GMT

Der Tipp war super!! Es klappt wenn ich =YTD auf einem Textfeld eingebe, dann kommt auch das richtige Ergebnis aber sobald ich es in die Tabelle packe ist das Ergebnis falsch.

Ich habe das so in mein Skript geladen:

So sah die Formel aus:

& das Ergebnis so:

Das YTD sollte aber für 2023 Bspw. 3,41% sein. Hatte schon ein wenig rumprobiert aber konnte das Ergebnis nicht erreichen.

Hast du vielleicht eine Idee woran es liegen könnte?

Re: Calculating YTD in Qlick View

marcus_sommer — Tue, 18 Jun 2024 06:02:36 GMT

Lass erstmal das num() weg, um eventuelle Fehl-Formatierungen auszuschließen. Dann erstelle noch weitere parallele Formeln, mit avg() + count() sowohl mit dem Flag in der Set Analysis, als auch ohne - und in einem Folgeschritt nimm auch mal ein/zwei weitere Dimensionen dazu, wie die Fund-Felder. Werden wirklich die erwarteten Werte angezeigt?

Re: Calculating YTD in Qlick View

lennart_mo — Tue, 18 Jun 2024 10:46:57 GMT

Wenn ich mir die Formel so anschaue ist deine Berechnung einfach eine andere. In deiner ursprünglichen Berechnung hast du ja die prozentuale Steigerung der [Adjusted NAV] im Vergleich zum Jahresanfang berechnet, aktuell summierst du alle [Adjusted NAV] Werte, für die YTD=1 gilt.

Da muss also deine Formel entsprechend angepasst werden.

Re: Calculating YTD in Qlick View

EL_MAR — Wed, 17 Jul 2024 08:09:44 GMT

Still searching for the right answer

Re: Calculating YTD in Qlick View

marcus_sommer — Wed, 17 Jul 2024 12:01:49 GMT

Ich denke, es gibt hier tiefergehende Herausforderungen mit den Daten und/oder Datenmodell und/oder den entsprechenden Ableitungen. Lass die YTD Bedingung und alle Formatierungen erstmal raus und nimm nur sum/avg/count des Feldes + die Quoten-Berechnung - gegen das Jahr sowie auch ein/zwei granularere Dimensionen. Werden hier jetzt die erwarteten Ergebnisse abgebildet?

Re: Calculating YTD in Qlick View

lennart_mo — Wed, 17 Jul 2024 14:16:32 GMT

Hallo @EL_MAR ,

Wie schon in meiner vorherigen Antwort geschrieben solltest du dir nochmal deine Formel anschauen, da diese aktuell von der in deinem ursprünglichen Post abweicht.

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

=Num((Sum({<YTD={1}>}[Adjusted NAV]),'##,##%')

Deine neue Formel bildet also nur den rot markierten Teil aus der alten Formel ab.

Anhand deines Skripts würde ich also folgende Formel vorschlagen:

=Num(
(Sum({<YTD={1}>}[Adjusted NAV])
-Sum({<DayNumber={1}>}[Adjusted NAV]))
/Sum({<DayNumber={1}>}[Adjusted NAV]),'##,##%')

Eventuell musst du auch die Bedingung für YTD = 1 anpassen, da gemäß deiner alten Formel ja nur über solche Werte eine Summe gebildet wird, für die Valuationdate = max(Valuationdate) gilt.

Re: Calculating YTD in Qlick View

EL_MAR — Thu, 26 Sep 2024 07:08:18 GMT

I created a quick fix which is working for me. The thing that was weirding out the table was the dimension, because trough the dimension Year that I had and the years in the formula their interacted with oneanother. To eliminate that I left out the dimension and created the formula for YTD:

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

In the tab Presentation I have the title 2024, as written in the example. I did that for each year and got the following table:

The next Problem was to have the Ultimo Date (last working date of the month) to calculate the correct YTD. I did that in the script that filtered for Ultimo 1, so if it is Ultimo it produces a 1. With that I implemented a filter, so that just the ultimo dates are calculated. If not activated it calculates the YTD from the Min Date to the overall Max Date.