Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Sebastiaandb
New 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 (2)
1 Solution

Accepted Solutions
Sebastiaandb
New Contributor III

Re: Need help for a running total with multiple dimensions!

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))
   

1 Reply
Sebastiaandb
New Contributor III

Re: Need help for a running total with multiple dimensions!

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))