Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- Accumulation count distinct in text box

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

jmialoundama

Creator III

2022-07-18
10:29 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 ?

Thanks in advance

186 Views

1 Solution

Accepted Solutions

Or

MVP

2022-07-18
02:34 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

143 Views

6 Replies

Or

MVP

2022-07-18
10:37 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

180 Views

jmialoundama

Creator III

2022-07-18
10:48 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:

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.

Thanks in advance

173 Views

Or

MVP

2022-07-18
11:16 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

158 Views

jmialoundama

Creator III

2022-07-18
11:24 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

155 Views

Or

MVP

2022-07-18
02:34 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

144 Views

jmialoundama

Creator III

2022-07-18
04:49 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks you for your help 🙂

135 Views