Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
rzaruvne
Contributor II
Contributor II

Calculate cumulative data in a table

rzaruvne_0-1746808676676.png

 

 

In the column "Total", I have a calculated measure, given by the following expression:

 

Count(DISTINCT {
<
origemEventoAtencao = {'1', '2', '3'},
tipoInternacao = {'1','2','3','4','5'},
regimeInternacao = {'1', '2'},
guiaSolicitacaoInternacao = {"*"},
dataRealizacao = {
">=$(=Date(AddMonths(Max(dataCalendario), -12)))<=$(=Date(Max(dataCalendario)))"
}
>
} guiaSolicitacaoInternacao)

+

Count(DISTINCT {
<
diariasUTI = {">0"},
guiaSolicitacaoInternacao = {"*"},
dataRealizacao = {
">=$(=Date(AddMonths(Max(dataCalendario), -12)))<=$(=Date(Max(dataCalendario)))"
}
>
} guiaSolicitacaoInternacao)

 

In the column "Contribuição individual", the expression is a simple ratio, being the expression above divided by the sum of all values of "Total".  

 

My problem is to calculate the cumulative percentage. From the example, the values of "Percentual acumulado" must be presented as shown in the figure below

rzaruvne_1-1746809116632.png

I have already tried using the above, range sum and row functions, however, none of them generated the cumulative sum, and it also messed up the order of the rows.

If it helps, the rows are sorted like this:

rzaruvne_2-1746809302121.png

 

 

 

Labels (2)
3 Replies
rubenmarin1

Hi, usually to cummulative value could be calculated as: RangeSum(Above([ExpressionToAccumulate],0,Rowno(TOTAL)))

Or, if the table has more than one dimension:

RangeSum(Above(TOTAL [ExpressionToAccumulate],0,Rowno(TOTAL)))

rzaruvne
Contributor II
Contributor II
Author

Hi, Rubenmarin.

 

Thank you for your contribution.

I used RangeSum(Above(TOTAL [ExpressionToAccumulate],0,Rowno(TOTAL)))
The cumulative sum is happening, but it is not respecting the order of the rows, as defined in the sort order. I believe that the expression imposes a classification, going over what is defined.

 

rzaruvne_4-1747140195568.png

 

The expression i used in [ExpressionToAccumulate] is big, but i think its important to visualize my problem.

 

RangeSum(Above(TOTAL Num(
  (
    Count(DISTINCT {
      <
        origemEventoAtencao = {'1', '2', '3'},
        tipoInternacao = {'1','2','3','4','5'},
        regimeInternacao = {'1', '2'},
        guiaSolicitacaoInternacao = {"*"},
        dataRealizacao = {
          ">=$(=Date(AddMonths(Max(dataCalendario), -12)))<=$(=Date(Max(dataCalendario)))"
        }
      >
    } guiaSolicitacaoInternacao)
    +

    Count(DISTINCT {
      <
        diariasUTI = {">0"},
        guiaSolicitacaoInternacao = {"*"},
        dataRealizacao = {
          ">=$(=Date(AddMonths(Max(dataCalendario), -12)))<=$(=Date(Max(dataCalendario)))"
        }
      >
    } guiaSolicitacaoInternacao)
  )
  /
  (
    Count(
      TOTAL DISTINCT {
        <
          origemEventoAtencao = {'1','2','3'},
          tipoInternacao = {'1','2','3','4','5'},
          regimeInternacao = {'1','2'},
          guiaSolicitacaoInternacao = {"*"},
          dataRealizacao = {
            ">=$(=Date(AddMonths(Max(dataCalendario), -12)))<=$(=Date(Max(dataCalendario)))"
          }
        >
      } guiaSolicitacaoInternacao
    )
    +
    Count(
      TOTAL DISTINCT {
        <
          diariasUTI = {">0"},
          guiaSolicitacaoInternacao = {"*"},
          dataRealizacao = {
            ">=$(=Date(AddMonths(Max(dataCalendario), -12)))<=$(=Date(Max(dataCalendario)))"
          }
        >
      } guiaSolicitacaoInternacao
    )
  )
  
),0,Rowno(TOTAL)))

 

As we can see, what it does is to count guiaSolicitacaoInternacao, given some parameters and divide the number by the total of the count of all rows. I believe the table is now being sorted by guiaSolicitacaoInternacao, but this would be wrong. It needs to be sorted by the calculated measures "contribuição individual" and then by "Data da última internação" 

 

marcus_sommer

The use of rowno() disabled the sorting within an object. You may try to bypass this restriction with a calculated dimension, like:

aggr(dual(MyDimension, rank(MyExpression), MyDimension)