Forum Discussion

brian_s's avatar
brian_s
Qrew Trainee
15 days ago

Dashboard Widget - calculated columns based on a table? Looking for the best way to proceed.

Hi all.

I have a request to create a dashboard widget that has potentially 7 calculated columns. I am not sure the best way to accomplish this.  I am relatively new to QuickBase and I have taken some of the training, but this seems a little beyond what I learned. Its also only the second app I have worked on. 

In a nutshell, I have a table we can call DATA, in that table are Market Classes, Close Dates, and $$ Values. 

Example:  DATA 

Market Class       CloseDate         $$Value

Pepsi                      1/13/25              100.00

Coke                      1/21/25               85.00

Fanta                      6/1/25                 200.00

Coke                       3/20/25              150.00

Pepsi                       2/12/25              60.00

I am looking to put a widget on the front page that shows something like the following:

Market Class    0 day   10 days    30 days     70 days     90 days     Total

Pepsi               100.00                      60.00                                              160.00

Coke                                85.00                          150.00                          235.00

Fanta                                                                                       200.00       200.00

Totals                100.00   85.00       60.00         150.00      200.00       595.00

 

Basically, I want to summarize all orders for a class by the date so I can see when certain deals are expected to close. I also want the totals of both for all orders in a class and and also for each time frame. 

I thought I could use report formulas, but unless I am mistaken, they are limited to 3.  I thought about trying to build a new Table, but I am thinking that might be overkill.

I am looking for any good suggestions on the best way to proceed here. Any suggestions putting me on the right path would be greatly appreciated. 

  • OK, lets take this step by step.  You are asking for how to summarize that data, and for that you want to create a Summary Report.  Th wrinkle here is that you want the columns headings in a particular grouping relative to today.

    I suggest making a new text field called [Close Date Bucket] and contriving to have it sort properly in alpha sequence.

    For example:

    IF(

    [Close Date] < Today(), "  Past days",

    [Close Date] <= Today() + Days(10), " 10 days",

    [Close Date] <= Today() + Days(30), " 30 days",

    [Close Date] <= Today() + Days(10), " 70 days",

    [Close Date] <= Today() + Days(10), " 90 days", "Over 90 days")

     

    Then make a summary report summarizing $$Value where the rows are by Market Class and enable column Grouping on [Date Bucket], 

    Once you have that Summary Report perfected you can put it on a Dashboard or a Home Page.