# Qlik Sense App Development

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

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

Labels (3)

• ### Set Analisys

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