Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
hope i´ve explained well. thanks for any help or guidance on how to aproach this problem!!
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?