# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Nov. 30th, Webinar! Do More With Qlik Cloud Catalog & Lineage: REGISTER
cancel
Showing results for
Did you mean:
Creator III

## Accumulation count distinct in text box

Hi, I do this formula to obtain cumulative count in table and it's work like i want :

=RangeSum(Above(Count({<categorie={'Anomalie'},rapporteur_interne={'Non'},annee_periode = {'\$(=Max(annee_periode))'},num_mois_periode = {'<=\$(=Max(num_mois_periode)-1)'}*{'>=1'}>} DISTINCT num_mantis),0,RowNo()))

But when i make this formula in text box , it's no work and i obtain this number

1) How can i get cumulative count in my text box ?

Labels (4)

• ### Visualization

1 Solution

Accepted Solutions
MVP

With no sample data, it's hard for me to try and troubleshoot this, but the general gist is that you want to count the number of distinct tickets per month, and then you want to aggregate that figure over all the months, hence

Sum( {Set Analysis} Aggr(Count( {Set Analysis} distinct num_tickets ) , year_periode))

Note that the specific location of the Set Analysis depends on the exact data and exact results you want to achieve, and you may need to either move it around or repeat the set analysis in multiple places - have a look at Henric's explanation here:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

6 Replies
MVP

What exactly is this formula supposed to accumulate in a text box? There are no lines, so there's nothing to accumulate. If you're looking for the total figure, it seems like you should just be using a regular Count() expression, or (if it is necessary to count per number of month), a sum(aggr(Count Whatever),number_of_month))

Creator III
Author

Hi @Or ,

I want to get the number of tickets for all the year 2022.
Here is an example of my data with a table in Excel:

As you can see in July I owe with 306 tickets and not 106.

In QlikView , I put this formula in a table and I get the correct numbers, i.e. in July (number_of_month = 7) , I find the 306 tickets :

=RangeSum(Above(Count({<categorie={'Anomalie'},rapporteur_interne={'Non'},annee_periode = {'\$(=Max(annee_periode))'},num_mois_periode = {'<=\$(=Max(num_mois_periode)-1)'}*{'>=1'}>} DISTINCT num_mantis),0,RowNo()))

But when I put this same formula in a text box , the formula doesn't work. I don't get the 360 tickets.

MVP

Once again, there is nothing to accumulate, so you won't get any result from this formula - there's nothing "above" in a text box. You'll likely need to use the Sum(Aggr(Count())) formula I mentioned above, adapting it with your set analysis of course.

Creator III
Author

@Or ,

I had also tried the following formula, but it does not work as I wish.

=Sum(Aggr(Count({<periode=,num_periode=, categorie={'Anomalie'}, year_periode ={'\$(=Max(year_periode ))'}>}distinct num_tickets),year_periode ))

I'll try to dig into it and see where the problem is.

=count({<periode=,annee_periode=, categorie={'Anomalie'},rapporteur_interne={'Non'}, annee_periode ={'\$(=Max(annee_periode))'}>}distinct num_mantis)

MVP

With no sample data, it's hard for me to try and troubleshoot this, but the general gist is that you want to count the number of distinct tickets per month, and then you want to aggregate that figure over all the months, hence

Sum( {Set Analysis} Aggr(Count( {Set Analysis} distinct num_tickets ) , year_periode))

Note that the specific location of the Set Analysis depends on the exact data and exact results you want to achieve, and you may need to either move it around or repeat the set analysis in multiple places - have a look at Henric's explanation here:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Creator III
Author

Thanks you for your help 🙂

Tags
Community Browser