Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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))
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
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.
@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)
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
Thanks you for your help 🙂