Forum Discussion

BradElmore's avatar
BradElmore
Qrew Assistant Captain
8 years ago

Summary field Count distinct

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
Ask about Quickbase functionality
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    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)
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      8 years ago
      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.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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)
  • BradElmore's avatar
    BradElmore
    Qrew Assistant Captain
    8 years ago
    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?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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  
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    8 years ago
    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
    • StanleyParret's avatar
      StanleyParret
      Qrew Member
      8 years ago
      is there an example of using Script/Underscorejs that you could point us to?
    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      8 years ago
      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/
  • IsaacSacolick1's avatar
    IsaacSacolick1
    Qrew Member
    7 years ago
    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.
  • IsaacSacolick1's avatar
    IsaacSacolick1
    Qrew Member
    7 years ago
    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,.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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.
    • RubyKapil2's avatar
      RubyKapil2
      Qrew Captain
      7 years ago
      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. 
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      I suggest that you post this as a new question so more people will see it.
  • hhersch's avatar
    hhersch
    Quickbase Staff
    7 years ago
    We will be releasing a native count distinct summary field in February or March.
    • EvanMartinez's avatar
      EvanMartinez
      Quickbase Staff
      7 years ago
      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.