Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

New Contributor

aggr with multiple dimensions with complex set analysis

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 2 Level 3

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

Labels (3)