BRIANHINSHAW
Qrew Member
Joined 5 years ago
User Profile
User Widgets
Contributions
Re: Trying to format with 2 decimal places
Hi Julie, Here's 3 examples of how I've done this in a metric report. If(IsNull([January]),"No Actuals", Case( [Measurement], "Total", "<div style='color: " & If([January] < [Metric Monthly Objective],"#FF0000",[January] >= [Metric Monthly Objective],"#228B22") & "'>"&If([Metric Type]="Currency","$")&ToFormattedText(Round([January],0.01),"comma_dot")&Case([Metric Type],"Currency","","Percent","%")&"</div>", "Financial", "<div style='color: " & If([January] < [Metric Monthly Objective],"#FF0000",[January] >= [Metric Monthly Objective],"#228B22") & "'>"&If([Metric Type]="Currency","$")&ToFormattedText(Round([January],0.01),"comma_dot")&Case([Metric Type],"Currency","","Percent","%")&"</div>", "Average", "<div style='color: " & If([January] < [Metric Monthly Objective],"#FF0000",[January] >= [Metric Monthly Objective],"#228B22") & "'>"&If([Metric Type]="Currency","$")&ToFormattedText(Round([January],0.01),"comma_dot")&Case([Metric Type],"Currency","","Percent","%")&"</div>", ToText([January]) )91Views0likes0CommentsRe: Create a Dashboard from Multiple Reports with Similar Fields
Create a "summary" table to collect the data for each patient (assuming patient ID is a key). The columns would look like Study 1 Current Timepoint, Study 1 1-Month Visit, etc. Then build a pipeline that triggers on edit to perform a copy records from Study 1 table to the fields in the summary table. Build your dashboard report off of the summary table to show the columns desired. You might could do the same with a relationship from each Study table to the Summary table, but have to get the patient ID (or other key) in the summary table for the lookups to collect the data. Depending on how many patients you have, you may want to use a button on your dashboard to take them to the report. Ask some filtering questions first and then display the results. Each time you have a report on a dashboard home tab it will pull all that data before it can load and display.59Views0likes0CommentsRe: Duration field in Pipeline Outlook channel
Using API records call I have a PO duration field that I pull into a pipeline. Comes from PO End date - PO Start date. The goal is to take the number of months for the PO and break the PO amount into what the monthly invoice forecast would be and create a child record for each month. { "to":"insert appID", "data": [ {% for num in range (quickbase_on_new_event_1.po_duration | int) %} { "6": { "value": "{{quickbase_on_new_event_1.po_start_date + time.delta(months= (num+1)) }}" {# FID 6 month #} }, "7": { "value": "{{quickbase_on_new_event_1.po_monthly}}" {# po monthly to FID 7 amount #} }, "8": { "value": "{{quickbase_on_new_event_1.po_number}}" {# related po FID 8 #} } } {% if loop.last == false %},{% endif %} {%- endfor %} ] } ------------------------------ BRIAN HINSHAW ------------------------------6Views0likes0CommentsRe: Multiple IF or Case statement for metric presentation
A QB Ninja helped clear this up for me and simplify the code. If(IsNull([2022 Actual YTD]),"No Actuals", Case( [Measurement], "Total", "<div style='color: " & If([2022 Actual YTD] < [2022 Target Low],"#FF0000",[2022 Actual YTD] >= [2022 Target High],"#228B22") & "'>"&If([Metric Type]="Currency","$")&ToFormattedText(Round([2022 Actual YTD],0.01),"none_dot")&Case([Metric Type],"Currency","M","Percent","%")&"</div>", "Average", "<div style='color: " & If([2022 Actual AVG YTD] < [2022 Target Low],"#FF0000",[2022 Actual AVG YTD] >= [2022 Target High],"#228B22") & "'>"&If([Metric Type]="Currency","$")&ToFormattedText(Round([2022 Actual AVG YTD],0.01),"none_dot")&Case([Metric Type],"Currency","M","Percent","%")&"</div>", ToText([2022 Actual AVG YTD]) ) ) ------------------------------ BRIAN HINSHAW ------------------------------19Views0likes0CommentsRe: Franchise Locator
You need Pythagoras to help here for some background: search QB community for https://community.quickbase.com/search?executeSearch=true&SearchTerm=pythagoras&l=1 https://community.quickbase.com/communities/community-home/digestviewer/viewthread?GroupId=103&MID=30357&CommunityKey=d860b0f8-6a48-487b-b346-44c47a19a804&tab=digestviewer - look for Posted 04-24-2014 13:31 entry https:/gridforcetech.com/use-quick-base-pipelines-to-reverse-geocode-addresses-with-google-maps-api-part-1/ Watch Kirk's video - closest customers to a given Latitude and Longitude https://community.quickbase.com/communities/community-home/digestviewer/viewthread?GroupId=43&MessageKey=2ea7d1af-9374-42a6-8182-6f1f7a545547&CommunityKey=157a0aac-5622-4134-8b0a-ae35c9b68e53&tab=digestviewer ------------------------------ BRIAN HINSHAW ------------------------------6Views0likes2CommentsMultiple IF or Case statement for metric presentation
Have a scorecard where the metric may be a total or an average. Based on that setting, trying to build formula rich text to show the appropriate data. Got half of it working for Total. How would I expand to support Measurement being an Average or another measurement type? If([Measurement] ="Total", If ([2022 Actual YTD] < [2022 Target Low], Case([Metric Type], "Currency", "<span style='color: #FF0000'>"&"$"&ToFormattedText(Round([2022 Actual YTD],0.01),"none_dot")&"M"&"</span>", "Percent", "<span style='color: #FF0000'>"&ToFormattedText(Round([2022 Actual YTD],0.01),"comma_dot")&"%"&"</span>", "Number", "<span style='color: #FF0000'>"&ToFormattedText(Round([2022 Actual YTD],0.01),"none_dot")&"</span>", ""), If ([2022 Actual YTD] >= [2022 Target High], Case([Metric Type], "Currency", "<span style='color: #228B22'>"&"$"&ToFormattedText(Round([2022 Actual YTD],0.01),"none_dot")&"M"&"</span>", "Percent", "<span style='color: #228B22'>"&ToFormattedText(Round([2022 Actual YTD],0.01),"comma_dot")&"%"&"</span>", "Number", "<span style='color: #FF0000'>"&ToFormattedText(Round([2022 Actual YTD],0.01),"none_dot")&"</span>", ""), //otherwise "No Actuals")) ) ------------------------------ BRIAN HINSHAW ------------------------------