aggr with multiple dimensions with complex set analysis

Alfovic — Sat, 16 Nov 2024 06:06:51 GMT

hi everyone!, this is my first post!

i´ve been developing an app en Qlik Sense which is very ambitious.

the problem is the following:

I have a QVD with all the daily prices of diferent funds and wanted to show a table with the returns of the diferent products

there is a 3 level hierarchy which is:

Company => Fund => Class

and i wanted to have a table that showed the returns of the selecction in every hierarchy level.

the formula i´ve wrote was:

if(count(distinct ID)=1,
(
sum({<Date= {"$(vMaxDate)"}>}Price)/
sum({<Date= {"$(v_YTD)"}>}Price)
-1)
,
sum(aggr(

sum({<Date= {"$(vMaxDate)"}>}Price*AUM)/
sum({<Date= {"$(v_YTD)"}>}Price)

,ID))
/sum({<Date = {"$(vMaxDate)"}>}AUM)

-1

)

which works fine for the last level of the hierarchy, but for level 1 and 2 theres the following situation where the result is not as expected.

if im calculating the return of a fund from 12/31/2018 to 03/31/2018 but the fund has 2 starting series and 4 ending series.

i see two posibilites. the first one is try to use the price at the first day of each series that started after 12/31/2018 or to just use the 2 series that existed by 12/31/2018.

also, the solution has to be by formula and not by script because there are several columns with diferent periods for the calculation.

but the i have not been able to write the formula. any help will be greatly aprreciated

heres is what i have been trying:

if(count(distinct ID)=1,
(
sum({<Date = {"$(vMaxDate)"}>}Price)/
sum({<Date = {"$(v_YTD)"}>}Price)
-1)
,
sum(aggr(

sum({<Date = {"$(vMaxDate)"}>}Price*AUM)/
sum({<Date = {"$(=rangemax((num(YearEnd(max(ALL Date),-1))-1),min( TOTAL <ID> Date)))"}>}Price)*
,ID, Date))
/sum({<Date = {"$(vMaxDate)"}>}AUM)
-1

)

here are some pictures:

Level 2Level 3

hope i´ve explained well. thanks for any help or guidance on how to aproach this problem!!

Re: aggr with multiple dimensions with complex set analysis

Alfovic — Thu, 11 Apr 2019 15:54:23 GMT

i´ve realized that calculating the based on the available date would not be right for comparing. so that leave me woth filtering by the clases that have all the time series.

is there a way to have something like:

sum({<Date= {"$(vMaxDate)"}>}Price*if(sum({<Date= {"$(v_YTD)"}>}Price)=0,0,1))

so that it only consider the price when theres a value for the first date?

topic aggr with multiple dimensions with complex set analysis in App Development

aggr with multiple dimensions with complex set analysis

Re: aggr with multiple dimensions with complex set analysis