Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Sebastiaandb
New Contributor III

Rangesum with multiple dimensions doesn't work accordingly -> need help with aggr function?

Dear Qlik users!

I'm only a week into Qlik and really enthousiastic about the possibilities it seems to have! 

Could someone help me with the following problem?

I made a script that shows the running total of the actual costs on basis of the selected month filter. For example, if the user clicks on march as in the picture, it sums up the costs of jan, feb and march (left table in picture). However, when i copied the table and included an extra dimension to see the running total per cost categorie (INTERN_LAAG_2_OMSCHRIJVING) then the script doesn't seem to work the way i want it to work (right table in picture). For example, the YTD personeelkosten should be 328K. With the current script it seems to sum the rows but not per dimension. 

I've searched for a soluation on the forums and i tried to use the aggr function but with no success (see Script i tried to use for RealisatieYTD)

Can somebody help me to fix the script? I really want to learn what i do wrong.

 

Script Realisatie YTD 

if(Date([JAAR_PERIODE_DATE.autoCalendar.YearMonth])< (date([Rapportagemaand.autoCalendar.YearMonth])),
RangeSum(Above(total( sum( {<
INTERN_LAAG_1_OMSCHRIJVING = {"Operationele kosten"},
Maand
>}
$(vRealisatietot)))
, 0, RowNo())))

Script vRealisatietot

if(Date([JAAR_PERIODE_DATE.autoCalendar.YearMonth])< (date([Rapportagemaand.autoCalendar.YearMonth])), REALISATIE)

Script i tried to use for RealisatieYTD

if(Date([JAAR_PERIODE_DATE.autoCalendar.YearMonth])< (date([Rapportagemaand.autoCalendar.YearMonth])),
aggr(RangeSum(Above(total( sum( {<
INTERN_LAAG_1_OMSCHRIJVING = {"Operationele kosten"},
Maand
>}
$(vRealisatietot)))
, 0, RowNo())), INTERN_LAAG_2_OMSCHRIJVING, Maand)

 

Qlik.PNG

Labels (3)