Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
nmarinelli
Partner
Partner

Count Distinct + Set Analysis and aggregation

Hi,

I am trying to display a chart of the number of accounts purchasing a given over time. I have fields MonthYear, account_id, product, and sales, and want to display the count of account_id's with sales>0 in each month by product.

I'm struggling to determine the set analysis for my chart. measure-- ex: COUNT(DISTINCT{<account_id = {"=SUM(sales)>0"}>}account_id ) gives the accounts who have ever made a purchase, not just a purchase in the current month.

Labels (2)
1 Solution

Accepted Solutions
y_grynechko
Creator III
Creator III

In theory this is a correct calc: COUNT(DISTINCT{<account_id = {"=SUM(sales)>0"}>}account_id 

if you add it to the line chart with MonthYear as a first dim it should group measure values by the MonthYear.

After adding second dim, product, you should get multiple product lines grouped by the month. 

Can you share the screen? 

View solution in original post

5 Replies
y_grynechko
Creator III
Creator III

Hey, 

are you trying to show in on the bar chart with MonthYear as a dimension?

 

nmarinelli
Partner
Partner
Author

Yes a line chart with MonthYear and Product as dimensions

y_grynechko
Creator III
Creator III

In theory this is a correct calc: COUNT(DISTINCT{<account_id = {"=SUM(sales)>0"}>}account_id 

if you add it to the line chart with MonthYear as a first dim it should group measure values by the MonthYear.

After adding second dim, product, you should get multiple product lines grouped by the month. 

Can you share the screen? 

View solution in original post

sunny_talwar

Try this

Count(DISTINCT Aggr(If(Sum(sales) > 0, account_id), account_id, month))

 

nmarinelli
Partner
Partner
Author

It was working all along, the error was with my data model! Interesting that Sunny's response gives me a different result, but only ever so slightly. Will have to dive into the set analysis to understand why.