topic Re: Count Distinct + Set Analysis and aggregation in App Development

Count Distinct + Set Analysis and aggregation

nmarinelli — Sat, 16 Nov 2024 04:03:14 GMT

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.

Re: Count Distinct + Set Analysis and aggregation

y_grynechko — Thu, 21 Nov 2019 18:13:34 GMT

Hey,

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

Re: Count Distinct + Set Analysis and aggregation

nmarinelli — Thu, 21 Nov 2019 18:23:26 GMT

Yes a line chart with MonthYear and Product as dimensions

Re: Count Distinct + Set Analysis and aggregation

y_grynechko — Thu, 21 Nov 2019 18:28:31 GMT

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?

Re: Count Distinct + Set Analysis and aggregation

sunny_talwar — Thu, 21 Nov 2019 18:41:16 GMT

Try this

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

Re: Count Distinct + Set Analysis and aggregation

nmarinelli — Thu, 21 Nov 2019 19:40:03 GMT

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.