Summary field Count distinct

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • In Progress
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

  • 494 Points 250 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 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,438 Points 20k badge 2x thumb
True trend reporting.

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 50,464 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

  • 51,046 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

  • 494 Points 250 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

  • 51,046 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

  • 26,322 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

  • 26,322 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/