Qlik Community

Qlik Sense App Development

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

Highlighted
Alfovic
New Contributor

aggr with multiple dimensions with complex set analysis

hi everyone!, this is my first post! Smiley Very Happy 

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

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

Labels (3)
1 Reply
Alfovic
New Contributor

Re: aggr with multiple dimensions with complex set analysis

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?