Count Distinct + Set Analysis and aggregation

[nmarinelli]

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.

[y_grynechko]

Hey, 

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

[nmarinelli]

Yes a line chart with MonthYear and Product as dimensions

[y_grynechko · Accepted solution]

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? 

[sunny_talwar]

Try this

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

[nmarinelli]

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.