Summary field Count distinct

  • 0
  • 1
  • Question
  • Updated 2 months ago
  • Answered
I'm this is impossible for Quickbase....but I wanted to ask. I have a Parent Table (customers) and a child table (orders)....

I want to see a which customers have placed an order in each month over the past 12 month.

I want to create a Summary field that will display a number from 0-12 -- and I would like to do this by using a Distinct Count on a Month field within my orders table --- with a filter set on an order date on or after 12 months ago....

So if there are 1000 orders spread out over (Month Field - may, june, july, aug) --- My summary field would = 4.


Thanks for any advice
Photo of rocketc

rocketc

  • 774 Points 500 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,678 Points 20k badge 2x thumb
This is possible. 
But its tricky to set up.


To get a true accounting and summary of orders like this, you need to have a joined table with summary records.

So if you have 10 companies and you want to track each of them by month, you'll have to create 10x12=120 summary records to get the data.

You will need a months table, and your "Order Summaries" table that will have all the joined records.

There are more details to follow if this is the route you want to go.

(I'm sure somebody will suggest just making 12 summary fields and doing a rolling 12 month report, but that will only solve your immediate ask, and any future reporting will be stunted.  I'd go the summary table route)
Photo of Matthew Neil

Matthew Neil

  • 31,678 Points 20k badge 2x thumb
True trend reporting.

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,550 Points 50k badge 2x thumb
Yes, I agree, that little report can be embedded on the customer record.  But on a report, you might also want to have an indicator on a report of 1,000 customers as to which ones to contact to ask whey they now hate you or thank them for their sales increase.  It would be good to have both the indicator for reports and the chart on the customer record.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,522 Points 50k badge 2x thumb
This can be done with 12 summary fields.

The first one would be for the count of orders in the previous month
The next would be a copy of that but During the last 2 months and not during the Previous 1 month.
The next would be during the previous 3 months and not during he previous 2 months.

Then you would build your final formula using those 12 fields.

Count(
[# orders month 1]>0,
[# orders month 2]>0,
[# orders month 3]>0,
.
.
[# orders month 12]>0)
Photo of rocketc

rocketc

  • 774 Points 500 badge 2x thumb
Mark,

Another question: Lets say that I create 12 summary fields.......and I want to create a formula field that will show me which customers sales are trending up and which are trending down (based on the summary fields) --- any suggests for what this formula should look like? or how best to go about this?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,522 Points 50k badge 2x thumb
Well, Assuming that your regular business is not highly seasonal I would make a new summary field for the Average sales in the last 3 months and then another summary field for Average sales in the last 12 months but excluding the recent previous last 3 months and then calculate the % increase or decrease  
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
Use script and underscorejs. This is a simple application of the method _.countBy():

http://underscorejs.org/#countBy

Why create all these extra fields which just clog up your application? Using script will allow you to solve this specific problem and open up a general way to solving all the other unique reports you want. Underscore has ever manner of data manipulation method you could desire including

filter, reject, reduce, sortBy, groupBy, indexBy, countBy
Photo of Stanley

Stanley

  • 60 Points
is there an example of using Script/Underscorejs that you could point us to?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
Underscore is a utility library that can perform any manner of calculation on JavaScript arrays and objects. It is used by QuickBase so you don't need to explicitly load it to use it. If you see "_" used in JavaScript code in this forum or the Pastie Database you are seeing underscore in action. The library is so powerful that I would be hard pressed to come up with a calculation on QuickBase data the it could not perform. 

http://underscorejs.org/
Photo of Isaac Sacolick

Isaac Sacolick

  • 120 Points 100 badge 2x thumb
Are there any better solutions available today around this issue? I'm trying to do the same, but my countd is by person and week so creating artificial table(s) to aid in the summary, or plugging in javascript are not good options.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,522 Points 50k badge 2x thumb
Can you explain your full question as to your objective?
Photo of Isaac Sacolick

Isaac Sacolick

  • 120 Points 100 badge 2x thumb
I have Contacts a parent table to Emails. Emails load in a filtered number of emails received/sent and uses Zapier to integrate. I'm trying to get a report answering, "How many people have I contacted (sent/received one or more emails) per week. Summing #emails would give me the total sent/received per week and I'm trying to get a distinct count of number contacts that I emailed every week,.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,522 Points 50k badge 2x thumb
hmmm I’m sure I posted a response but it did not get saved.

A partial solution.
Make a formula numeric field with a value of 1
Summary report on Maximum of that field.

That will give the counts but not the total by week as the Total will in fact be a Max.

Export to Excel

Make your totals in excel.
Photo of Ruby Kapil

Ruby Kapil

  • 1,050 Points 1k badge 2x thumb
I have a similar situation I have been trying to figure out.

Parent NODE to Child Poles.
Parent PERMITS to Child Poles.
No relationship between the two parents Node and Permits, however, all three tables are Child to DOCKS.

On NODE table, I want a distinct count of PERMITS for the Poles in that NODE. 

I can get # of Poles per NODE
I can get # of Poles per PERMIT
I can get # of Poles per DOCK, # of PERMITS per DOCK, # of NODES per DOCK.
I tried take "# of Permits per Dock Summary field" from DOCK and add as lookup field on Nodes, but that shows total number of Permits for the DOCK, not broken out for each Node which is what I need.

Just can't figure out how to show on the NODES table, the # of PERMITS.

I have ready some of your related posts but am not sure where to put Minimums or Maximums at which tables, what to lookup from where. 
Any advice?

Thanks. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,522 Points 50k badge 2x thumb
I suggest that you post this as a new question so more people will see it.
Photo of Harrison Hersch (QB)

Harrison Hersch (QB), Quick Base Sr. Product Manager (Platform)

  • 1,458 Points 1k badge 2x thumb
We will be releasing a native count distinct summary field in February or March.
Photo of Ruby Kapil

Ruby Kapil

  • 1,050 Points 1k badge 2x thumb
Any news on the release date?
Photo of Micro Dev

Micro Dev

  • 1,444 Points 1k badge 2x thumb
I think the release date is tomorrow.
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 11,346 Points 10k badge 2x thumb
I can confirm that the count distinct function went live in our release yesterday and you can now find it as a summary field option. If you want to know more about count distinct and the other features in our February release you can find the full release notes here