Skip to main content

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 
Search instead for 
Did you mean: 
jmialoundama
Creator III
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 : 

jmialoundama_0-1658154392437.png

=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

jmialoundama_1-1658154486661.png

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

Thanks in advance

Labels (4)
1 Solution

Accepted Solutions
Or
MVP
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

 

View solution in original post

6 Replies
Or
MVP
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))

jmialoundama
Creator III
Creator III
Author

Hi @Or ,

Thanks you for your reply. 

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:

jmialoundama_1-1658155542614.png

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

jmialoundama_2-1658155650498.png

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

Thanks in advance

 

Or
MVP
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.

jmialoundama
Creator III
Creator III
Author

@Or ,

I didn't quite understand your answer.

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)

Or
MVP
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

 

jmialoundama
Creator III
Creator III
Author

@Or 

Thanks you for your help 🙂