Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Edu_Zulueta
Contributor
Contributor

Cummulative count distinct users Bar Chart

Hi,

I would like to know how can I do the cumulative count distinct users in a Bar Chart, the dimension would be the field Prob and the result would be something like this:

table.PNG

 

 

 

 

 

 

And the bar chart:

chart.PNG

 

 

 

 

 

 

 

One user can has more than one Prob.

Then, the 9 distinct users in the Prob 6, are the 9 distinct users that have the Prob = 1 or/and Prob = 2 ... or/and Prob = 6.

And the 20 distinct users in the Prob 10, are the 20 distinct users which have Prob = 1 or/and Prob = 2... or/and Prob = 10.

Many thanks.

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

Try this

Table:
LOAD * INLINE [
    Prob, UserId
    1, ID_01
    1, ID_02
    2, ID_03
    2, ID_01
    3, ID_04
];

MaxProb:
LOAD Min(Prob) as MinProb
Resident Table;

LET vMinProb = Peek('MinProb');
TRACE $(vMinProb);

AsOfTable:
LOAD DISTINCT Prob as AsOfProb,
	 Prob - IterNo() + 1 as Prob
Resident Table
While Prob - IterNo() + 1 >= $(vMinProb);

Create a chart with AsOfProb as dimension and Count(DISTINCT UserId) as your expression

View solution in original post

8 Replies
Carlos_Reyes
Partner
Partner

There are multiple posts in the community regarding this topic.

https://community.qlik.com/t5/Qlik-Sense-App-Development/Accumulation-in-Qlik-Sense/m-p/670706

 

Review the enclosed sample

 

Edu_Zulueta
Contributor
Contributor
Author

 

Thanks Carlos and sorry, I didn't explain correctly the issue, because one user could have more than one Prob, then if one user has 2 Prob (you can see the example below) the desired output is to count it as only one user:

 

example.PNG

 

 

 

 

 

And with the following formula we are not excluding the repeated users in the previous Prob: 

rangesum( above( Count(distinct userId),0,rowno()))

I hope now it's more clear.

 

 

sunny_talwar

The best way to handle this would be to use The As-Of Table

Carlos_Reyes
Partner
Partner

Yep, The As-Of Table would be the optimum/elegant way to achieve this.
Edu_Zulueta
Contributor
Contributor
Author

Thanks Sunny ans also thanks to Carlos, 

But I am not an expert in Qlik and is difficult for me to understand the attached link,  I would appreciate if you can detail a bit how can I implent in my case the As-Of table.

many thanks

Edu 

sunny_talwar

Did you try to check the link? I guess if you need something specific, you will have to provide a sample with the expected output you expect to see from it? What if you shared in your initial post is a pre-aggregated data.

image.png

Edu_Zulueta
Contributor
Contributor
Author

Hi Sunny,

As I commented to Carlos, in my initial post I was wrong, I explained the issue as I need the cummulative count of users. Bu t the issue I am trying to reach is the cummulative count of distinct users, I hope with the figure below it will be more clear 🙂 :

Capture.PNG

 

 br edu

sunny_talwar

Try this

Table:
LOAD * INLINE [
    Prob, UserId
    1, ID_01
    1, ID_02
    2, ID_03
    2, ID_01
    3, ID_04
];

MaxProb:
LOAD Min(Prob) as MinProb
Resident Table;

LET vMinProb = Peek('MinProb');
TRACE $(vMinProb);

AsOfTable:
LOAD DISTINCT Prob as AsOfProb,
	 Prob - IterNo() + 1 as Prob
Resident Table
While Prob - IterNo() + 1 >= $(vMinProb);

Create a chart with AsOfProb as dimension and Count(DISTINCT UserId) as your expression

View solution in original post