Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Sebastiaandb
Contributor III
Contributor III

Need help for a running total with multiple dimensions!

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)

Labels (1)
1 Solution

Accepted Solutions
Sebastiaandb
Contributor III
Contributor III
Author

Found that you have to use a different variable for both tables.

For the right table i've used the following variable/script:

 

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

View solution in original post

1 Reply
Sebastiaandb
Contributor III
Contributor III
Author

Found that you have to use a different variable for both tables.

For the right table i've used the following variable/script:

 

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