Ready

RBD Tableau Calc-Field Harvest

Auto-extracted 2026-08-05 from ~/Documents/My Tableau Repository/Workbooks/ — Cole's encoded data logic. Raw dump; curate the important ones into rbd-data-dictionary.md.

====================================================================== RBD Datasources.twb — 80 calculated fields ======================================================================

Unique TranID

COUNTD([tran_id])

Sales Channel

6 Month LTV Filter

IF DATEDIFF('day',[Calculation_4168011138848129027],today())<=182.5 THEN 'Out' elseif DATEDIFF('day',[Calculation_4168011138848129027],[Calculation_1852175773966282752])<=182.5 THEN 'In' Else 'Out' END

2023 Invoiced

IF [type] = 'CustInvc' AND YEAR([created_date]) = 2023 THEN [net_amount] ELSEIF [type] = 'CashSale' AND YEAR([created_date]) = 2023 THEN [net_amount] END

2024 Invoiced

IF [type] = 'CustInvc' AND YEAR([created_date]) = 2024 THEN [net_amount] ELSEIF [type] = 'CashSale' AND YEAR([created_date]) = 2024 THEN [net_amount] END

1-Batik Booked Sales Previous Month

IF month([created_date])= month(today())-1 AND year([created_date])=year(today()) AND [class] IN ('Batik - Elementals', 'Batik - Collection', 'Batik - Hand Dyes', 'Batik - Other', 'Batik - Wideback', 'Precut Batik') THEn [net_amount] ELSE 0 END

net_amount_all_types

IF [type] IN ('CashRfnd', 'CustCred', 'RtnAuth', 'CustRfnd', 'CardRfnd')
THEN -1 * [net_amount] ELSE [net_amount]
END

At Once SO Based

IF ISNULL([anticipated_release_date]) THEN 'At Once' ELSEIF [created_date]<[anticipated_release_date] THEN 'Presale' ELSEIF [created_date]>=[anticipated_release_date] THEN 'At Once' ELSE 'At Once' END

Re-release Filter

IF ISNULL([re_release_date]) THEN 'In' ELSEIF [created_date] <= [re_release_date] THEN 'Out' else 'In' END

Last Sale Date

{fixed [rbd_collection]: max([created_date])}

Previous Month Booked Revenue

If month([created_date])= month(DATEADD('month',-1,today() )) AND year([created_date])=year(today()) THEN [net_amount] ELSE 0 END

YTD Revenue

if year(today()) = year([created_date]) THEN [net_amount] else 0 END

Dep-Sales Channel

IF [entity_name] = 'Fabric.Com' THEN 'Fabric.com' ELSEIF [entity_name] = 'Missouri Star Quilt Co, Inc.' THEN 'Missouri Star Quilt Co, Inc.' ELSEIF [entity_name] = 'Fat Quarter Shop' THEN 'Fat Quarter Shop' ELSEIF [entity_name] = 'Checker Notions Company Inc' THEN 'Checker' ELSEIF [category] = 'Distributor 1' THEN 'International Distributors' ELSEIF [category] = 'Distributor 2' THEN 'International Distributors' ELSEIF [nt_employee_name] = 'Firth, Sammy M' THEN 'Direct Sales Reps' ELSEIF [nt_employee_name] = 'Gray, Lori' THEN 'Direct Sales Reps' ELSEIF [nt_employee_name] = 'Porter, Teresa' THEN 'Direct Sales Reps' ELSEIF [nt_employee_name] = 'Taylor, Jessica' THEN 'Direct Sales Reps' ELSEIF [nt_employee_name] = 'Goldenberg, Jeffrey P' THEN 'Direct Sales Reps' ELSEIF [nt_employee_name] = 'Ross, Chris' THEN 'Direct Sales Reps' ELSEIF [nt_employee_name] = 'Winninger, Jill' THEN 'Direct Sales Reps' ELSEIF [nt_employee_name] = 'Goeckeritz, Matt J' THEN 'Direct Sales Reps' ELSEIF [nt_employee_name] = 'Cloward, Bret C' THEN 'Direct Sales Reps' ELSEIF [nt_employee_name] = 'Mathis, Lisa' THEN 'Direct Sales Reps' ELSEIF [nt_employee_name] = 'Lindner, Jody' THEN 'Direct Sales Reps' ELSEIF [nt_employee_name] = 'Miller, Mary' THEN 'Direct Sales Reps' ELSEIF [nt_employee_name] = 'Rurup, Gary' THEN 'Direct Sales Reps' ELSEIF [nt_employee_name] = 'Zimmerman, Cole' THEN 'Direct Sales Reps' ELSEIF [nt_employee_name] = 'Milligan, Angie' THEN 'Direct Sales Reps' ELSEIF [nt_employee_name] = 'Andersen, Gloria' THEN 'Direct Sales Reps' ELSEIF [nt_employee_name] = 'Herndon, Jami' THEN 'Direct Sales Reps' ELSEIF [nt_employee_name] = 'Deneault, Kim' THEN 'Independent Sales Reps' ELSEIF [nt_employee_name] = 'In-House,' THEN 'In House' ELSE 'Independent Sales Reps' END

monthName

DATENAME('month',[Calculation_1852175773966282752])

Case Packs Only

IF LEFT([rbd_item_id],2) = 'CP' THEN 'Case Pack' Else 'Other' END

Discount vs Full Price

IF [foreign_amount] = [net_amount] THEN 'Full Price' ELSEIF -(([net_amount]/[foreign_amount]) -1) <= .1 THEN '<10% Off' ELSEIF -(([net_amount]/[foreign_amount]) -1) <= .15 THEN '<15% Off' ELSEIF -(([net_amount]/[foreign_amount]) -1) <= .2 THEN '<20% Off' ELSEIF -(([net_amount]/[foreign_amount]) -1) <= .25 THEN '<25% Off' ELSEIF -(([net_amount]/[foreign_amount]) -1) <= .3 THEN '<30% Off' Else '>30% Off' END

Color Scheme

IF FINDNTH([rbd_item_id],'-',1) = 0 THEN NULL ELSEIF FINDNTH([rbd_item_id],'-',2) = 0 THEN right([rbd_item_id],len([rbd_item_id])- FINDNTH([rbd_item_id],'-',1)) ELSEIF FINDNTH([rbd_item_id],'-',2) - FINDNTH([rbd_item_id],'-',1) = 4 THEN MID([rbd_item_id],FINDNTH([rbd_item_id],'-',1)+1,FINDNTH([rbd_item_id],'-',2) - FINDNTH([rbd_item_id],'-',1)-1) Else NULL END

Price / Yd

SUM([net_amount]) / SUM([quantity])

Sales per SKU

SUM([net_amount]) /COUNTD([rbd_item_id])

Unique Customers

COUNTD([company_name])

First Order Tag

IF [created_date]=[Calculation_4168011138848129027] THEN 'First Order' else 'Subsequent Orders' END

AOV

sum([net_amount]) / COUNTD([tran_id])

Current Month Case Packs

If month([Calculation_1852175773966282752])= month(today()) AND year([Calculation_1852175773966282752])=year(today()) THEN [Case Packs (copy)_1825506009404993537] ELSE 0 END

Tran or Created Date

IF ISNULL([tran_date]) OR [tran_date] = DATE("1970-01-01") THEN [created_date] ELSE [tran_date] END

Up to Today

[tran_date] <= TODAY()

Booked Revenue

if [type]= 'SalesOrd' THEN [net_amount] else 0 END

Invoiced Revenue

IF [type] = 'CustInvc' THEN [net_amount] ELSEIF [type] = 'CashSale' THEN [net_amount] else 0 end

Unique Orders

COUNTD([tran_id])

ME Booked Current Month

IF month([Calculation_1852175773966282752])= month(today()) AND year([Calculation_1852175773966282752])=year(today()) AND [class] = 'Projects - ME' THEn [net_amount] ELSE 0 END

Last 7 Days

If [created_date] >= DATEADD('day',-8,today()) THEN [net_amount] else 0 END

Total Shops

COUNTD([entity_name])

Average Sales order

sum([net_amount]) / COUNTD([entity_name])

% of Total

sum([Calculation_3141331155803529217]) / sum([net_amount])

Batik Booked Sales Current Month

IF month([Calculation_1852175773966282752])= month(today()) AND year([Calculation_1852175773966282752])=year(today()) AND [class] IN ('Batik - Elementals', 'Batik - Collection', 'Batik - Hand Dyes', 'Batik - Other', 'Batik - Wideback', 'Precut Batik') THEn [net_amount] ELSE 0 END

2022 Invoiced

IF [type] = 'CustInvc' AND YEAR([created_date]) = 2022 THEN [net_amount] ELSEIF [type] = 'CashSale' AND YEAR([created_date]) = 2022 THEN [net_amount] END

Current Month

IF MONTH([Calculation_1852175773966282752]) = MONTH(TODAY()) then 'in' END

Difference

SUM([sqlproxy.0pub43y0718nkx18avobm1wjjlx0].[value])- sum([2023 Invoiced (copy)_394909434306228226])

Percent of Target

sum([2023 Invoiced (copy)_394909434306228226])/SUM([sqlproxy.0pub43y0718nkx18avobm1wjjlx0].[value])

Days of Month

DAY(DATEADD('day',-1,DATEADD('month',1,DATETRUNC('month',TODAY()))) )

Percent of Month

DAY(TODAY())/[Calculation_394909434313089032]

Pre-Sales

IF [created_date]<[anticipated_release_date] then 'Presale' END

2023 Presales YTD

IF [created_date]<DATEADD('year',-1,TODAY()) AND YEAR([anticipated_release_date])=2023 then [net_amount] END

2024 Presales

IF [created_date]<[anticipated_release_date] AND YEAR([anticipated_release_date])=2024 then [net_amount] END

Sales Channels

IF entity_name = 'Fabric.Com' THEN 'Fabric.com' ELSEIF entity_name = 'Missouri Star Quilt Co, Inc.' THEN 'Missouri Star Quilt Co, Inc.' ELSEIF entity_name = 'Fat Quarter Shop' THEN 'Fat Quarter Shop' ELSEIF entity_name = 'Checker Notions Company Inc.' THEN 'Checker' ELSEIF category = 'Big Box' THEN 'Big Box' ELSEIF category = 'Distributor 1' THEN 'International Distributors' ELSEIF category = 'Distributor 2' THEN 'International Distributors' ELSEIF [nt_employee_name] = 'Firth, Sammy M' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Gray, Lori' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Porter, Teresa' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Taylor, Jessica' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Goldenberg, Jeffrey P' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Ross, Chris' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Winninger, Jill' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Goeckeritz, Matt J' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Cloward, Bret C' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Mathis, Lisa' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Lindner, Jody' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Leonardo, Jody' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Miller, Mary' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Rurup, Gary' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Zimmerman, Cole' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Milligan, Angie' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Andersen, Gloria' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Herndon, Jami' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Deneault, Kim' THEN 'Independent Sales Reps' ELSEIF [nt_employee_name] = 'Hempfling, Felie' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Painter, Kristen' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Delaney, Scarlett' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Fogle, Chelsea' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Robinson, Gerri' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Freese, Angie' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Rushing, Kellie' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Maret, Anderson' THEN 'Independent Sales Reps' ELSEIF [nt_employee_name] = 'Eardley, Regina' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Statzer, Angela' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Barrera, Sarah' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Wu, Nellie' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Phippins, Robin' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Spratt, Wendy' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Grim, Nancy' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Dippiro, Christine' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'DiPirro, Christine' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Pike, Sara' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Dufur, Jennifer' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Mayer, Leisa' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Cartwright, Heather' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Anderson, Cindy' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Hince, Becky' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Krueger, Cory' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Culbertson, Cindy' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Wise, Jennifer' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Witte, Sarah' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Doron, Rich' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Collins, Mary' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Spinelli, Christine' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Anglin, Liza' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'In-House,' THEN 'In House' ELSE 'Independent Sales Reps' END

Sales Timing

If [Calculation_1852175773966282752] < IFNULL([last_call_date],[anticipated_release_date]) THEN 'Pre-Sales' elseif [Calculation_1852175773966282752] < DATEADD('day',10,[last_call_date]) THEN 'Pre-Sales' elseif [Calculation_1852175773966282752] < [anticipated_release_date] THEN 'Last Call' ELSEIF DATEDIFF('day', [anticipated_release_date], [Calculation_1852175773966282752]) <= 90 THEN '0-90 days post-release' ELSEIF DATEDIFF('day', [anticipated_release_date], [Calculation_1852175773966282752]) <= 180 THEN '90-180 days post-release' ELSEIF [Calculation_1852175773966282752] > [closeout_date] THEN 'Closeout' ELSE '+180 days post-release' END

Customer Age

IF DATEDIFF('day',[Calculation_4168011138848129027],[created_date]) <=30 THEN 'New Customer' ELSEIF DATEDIFF('day',[Calculation_4168011138848129027],[created_date]) <=30 THEN 'New Customer' ELSEIF DATEDIFF('day',[Calculation_4168011138848129027],[created_date]) <=182.5 THEN '0-6 Months' ELSEIF DATEDIFF('day',[Calculation_4168011138848129027],[created_date]) <=365 THEN '6-12 Months' ELSEIF DATEDIFF('day',[Calculation_4168011138848129027],[created_date]) <=547.5 THEN '12-18 Months' ELSEIF DATEDIFF('day',[Calculation_4168011138848129027],[created_date]) <=730 THEN '18-24 Months' ELSEIF DATEDIFF('day',[Calculation_4168011138848129027],[created_date]) <=912.5 THEN '24-30 Months' ELSEIF DATEDIFF('day',[Calculation_4168011138848129027],[created_date]) <=1095 THEN '30-36 Months' ELSEIF DATEDIFF('day',[Calculation_4168011138848129027],[created_date]) <=1277.5 THEN '36-42 Months' ELSEIF DATEDIFF('day',[Calculation_4168011138848129027],[created_date]) <=1460 THEN '42-48 Months' ELSEIF DATEDIFF('day',[Calculation_4168011138848129027],[created_date]) <=1642.5 THEN '48-54 Months' ELSEIF DATEDIFF('day',[Calculation_4168011138848129027],[created_date]) <=1825 THEN '54-60 Months' else '60+ Months'

END

First Order Date

{ FIXED [company_name]:MIN([created_date])}

% of Total CLOSED

SUM(if [is_closed] = 1 THEN [net_amount] Else 0 END) / SUM([net_amount])

At Once

IF [created_date]>[anticipated_release_date] THEN 'At Once' ELSE 'Presales' END

Is Big

IF entity_name = 'Fabric.Com' THEN 'Big' ELSEIF entity_name = 'Missouri Star Quilt Co, Inc.' THEN 'Big' ELSEIF entity_name = 'Fat Quarter Shop' THEN 'Big' ELSEIF entity_name = 'Checker Notions Company Inc' THEN 'Big' else 'Small' END

Avg Customer Revenue

SUM([net_amount]) / COUNTD([company_name])

Buying Frequency

COUNTD([tran_id])/ COUNTD([company_name])

12 Month LTV Filter

IF DATEDIFF('day',[Calculation_4168011138848129027],today())<=365 THEN 'Out' elseif DATEDIFF('day',[Calculation_4168011138848129027],[Calculation_1852175773966282752])<=365 THEN 'In' Else 'Out' END

Combine Hand Dyes

IF FINDNTH([rbd_item_id],'-',2) > 0 THEN LEFT([rbd_item_id],FINDNTH([rbd_item_id],'-',2)-1) Else [rbd_item_id] END

Sales per Sales Order

SUM([net_amount]) / COUNTD([tran_id])

Prints

LEFT([rbd_item_id],FIND([rbd_item_id],'-')-1)

Main vs Secondary

IF CONTAINS([rbd_collection],'Express Yourself') THEN 'Main' elseif RIGHT([Calculation_6522901143033700359],1) = '0' THEN 'Main' Else 'Secondary' END

Colection Delta

{ FIXED [rbd_collection] : MAX({ FIXED [rbd_collection], [rbd_item_id] : SUM([net_amount]) }) } - { FIXED [rbd_collection] : MIN({ FIXED [rbd_collection], [rbd_item_id] : SUM([net_amount]) }) }

Cancelled Total

IF [is_closed] = 1 then [net_amount] else 0 end

Unfulfilled orders placeholder

[net_amount]

Fulfilled orders placeholder

[net_amount]/100

Date

[ship_date]

Unique SKUs

COUNTD([item_sku])

Percent of Year

DATEDIFF('day',#2024-01-01#,TODAY()) / 365

Percent of 2023 Presales

sum([Calculation_394909434918600717])/SUM([Calculation_394909434918600717 1])-1

Case Packs (qty)

If FIND([purchase_description],"Casepack") > 0 THEN [quantity] ELSEIF FIND([purchase_description],"Case Pack") > 0 THEN [quantity] ELSEIF FIND([purchase_description],"case Pack") > 0 THEN [quantity] ELSEIF FIND([purchase_description],"Case pack") > 0 THEN [quantity] ELSEIF FIND([purchase_description],"case pack") > 0 THEN [quantity] ELSEIF FIND([purchase_description],"casepack") > 0 THEN [quantity] else 0 END

Notions ($$)

If class = 'Notion RBD' THEN [net_amount] ELSEIF class = 'Other Notion' Then [net_amount] end

Previous Month

IF MONTH([Calculation_1852175773966282752]) = Month(DATEADD('month',-1,TODAY())) THEN 'in' END

Previous Month At Once

If month([Calculation_1852175773966282752])= month(DATEADD('month',-1,TODAY())) AND year([Calculation_1852175773966282752])=year(today()) AND ISNULL([anticipated_release_date]) THEN [net_amount] ELSEIF month([Calculation_1852175773966282752])= month(DATEADD('month',-1,TODAY())) AND year([Calculation_1852175773966282752])=year(today()) and [Calculation_1852175773966282752] >= [anticipated_release_date] THEN [net_amount] ELSE 0 END

1-Previous Month At Once

MONTH([created_date])= month(today())-1 AND year([created_date])=year(today()) AND ISNULL([anticipated_release_date]) THEN [net_amount] ELSEIF MONTH([created_date])= month(today())-1 AND year([created_date])=year(today()) and [created_date] >= [anticipated_release_date] THEN [net_amount] ELSE 0 END

1-Prevoius Month Booked Revenue

If month([created_date])= month(today())-1 AND year([created_date])=year(today()) THEN [net_amount] ELSE 0 END

Current Month Notions

If month([Calculation_1852175773966282752])= month(today()) AND year([Calculation_1852175773966282752])=year(today()) THEN [Case Packs (qty) (copy)_4243587173808164865] ELSE 0 END

1-Previous Month Case Pack

If month([created_date])= month(today())-1 AND year([created_date])=year(today()) THEN [Case Packs (copy)_1825506009404993537] ELSE 0 END

1-Previous Month Notions

If month([created_date])= month(today())-1 AND year([created_date])=year(today()) THEN [Case Packs (qty) (copy)_4243587173808164865] ELSE 0 END

Current Month At Once

MONTH([Calculation_1852175773966282752])= month(today()) AND year([Calculation_1852175773966282752])=year(today()) AND ISNULL([anticipated_release_date]) THEN [net_amount] ELSEIF MONTH([Calculation_1852175773966282752])= month(today()) AND year([Calculation_1852175773966282752])=year(today()) and [Calculation_1852175773966282752] >= [anticipated_release_date] THEN [net_amount] ELSE 0 END

Discount vs Full Price (boolean)

IF [foreign_amount] = [net_amount] THEN 'Full Price' else 'Discount' END

1-ME Booked Previous Month

IF month([created_date])= month(today())-1 AND year([created_date])=year(today()) AND [class] = 'Projects - ME' THEn [net_amount] ELSE 0 END

Current Month Booked Revenue

If month([Calculation_1852175773966282752])= month(today()) AND year([Calculation_1852175773966282752])=year(today()) THEN [net_amount] ELSE 0 END

1-Previous Month Booked Revenue

If month([created_date])= month(DATEADD('month',-2,today() )) AND year([created_date])=year(today()) THEN [net_amount] ELSE 0 END

anticipated_release_month

DATE(DATETRUNC('month', [anticipated_release_date]))

====================================================================== RBD - Netsuite Datasources.twb — 0 calculated fields ======================================================================

====================================================================== Order Types - DD Reporting.twb — 7 calculated fields ======================================================================

SKU Prefix

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([sku],'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','')

Order - Item Count

{ FIXED [order_id]: count([product_id])}

price_extended

[price]*[quantity]

TOR

sum([Calculation_2127387925886205955] - [discount_amount])+ MAX([o_shipping_amount])

# of DD on Order

{ FIXED [order_id] : sum(IF [daily_deal]='true' THEN 1 ELSE 0 end )}

# of BOMs on Order

{ FIXED [order_id]: sum(IF [Calculation_2127387925884837888]='BOM' THEN 1 else 0 end)}

Order Type

IF [Calculation_2127387925884968961] = 1 AND [Calculation_3042040862933463040] = 1 THEN 'DD Only' ELSEIF [Calculation_2127387925884968961] > 1 AND [Calculation_3042040862933463040] = 1 THEN 'DD Included' ELSEIF [Calculation_3042040862935478273]>0 AND [Calculation_3042040862933463040] = 0 THEN 'BOM Order' Else 'Normal' END

====================================================================== Pre-sales - Last Call - After Release.twb — 4 calculated fields ======================================================================

Sales Timing

If [created_date] < IFNULL([last_call_date],[anticipated_release_date]) THEN "Pre-Sales" elseif [created_date] < DATEADD('day',10,[last_call_date]) THEN 'Pre-Sales' elseif [created_date] < [anticipated_release_date] THEN 'Last Call' ELSEIF DATEDIFF('day', [anticipated_release_date], [created_date]) <= 90 THEN '0-90 days post-release' ELSEIF DATEDIFF('day', [anticipated_release_date], [created_date]) <= 180 THEN '90-180 days post-release' ELSEIF [created_date] > [closeout_date] THEN 'Closeout' ELSE '+180 days post-release' END

Last 2 Weeks Amount

if [created_date]> DATEADD('day',-8,NOW()) THEN 'Last 7 Days' ELSEIF [created_date]> DATEADD('day',-15,NOW()) THEN '7-14 Days' else 'All Time' END

YTD Filter 2023

if [created_date] <= TODAY() AND YEAR([created_date]) = year(DATEADD("year",-1, TODAY() )) THEN 'In' Else 'Out' END

YTD Filter 2024

if [created_date] <= TODAY() AND YEAR([created_date]) = year(DATEADD("year",0, TODAY() )) THEN 'In' Else 'Out' END

====================================================================== Sales Rep Data.twb — 5 calculated fields ======================================================================

Sales Channel

Last 7 Days

If [created_date] >= DATEADD('day',-8,today()) THEN [net_amount] else 0 END

Total Shops

COUNTD([entity_name])

Average Sales order

sum([net_amount]) / COUNTD([entity_name])

% of Total

sum([Calculation_3141331155803529217]) / sum([net_amount])

====================================================================== Sales History.twb — 8 calculated fields ======================================================================

Greater than Today

IF [Date] > TODAY() THEN 'Greater' Else 'Lesser' ENd

Combined Sales

[Calculation_358247308704669707]+[Calculation_358247308704841740]

New Date (Future Adjustment)

IF [Calculation_358247308701761544] > TODAY() THEN TODAY() ELSE [Calculation_358247308701761544] END

New Date

IF [Calculation_358247308703289354] < [Calculation_358247308707737616] THEN [Calculation_358247308703289354] ELSEIF [Calculation_358247308703289354] > [Calculation_358247308707737616] THEN [Calculation_358247308703289354] ELSEIF [Date] > [Calculation_358247308707737616] THEN [Date] ELSe [Anticipated Release Month] END

Actual Ship Date (Date)

DATE([Actual Ship Date])

Unfulfilled Sales

IF [Quantity Billed]> [Quantity] THEN 0 elseif [Status] = 'Closed' THEN 0 elseif [Reorderable] = 'Discontinued' THEN 0

ELSE([Quantity] - [Quantity Billed])*[Item Rate] END

Fulfilled Sales

IF [Quantity Billed] = 0 THEN [Quantity Fulfilled/Received]*[Item Rate] ELSE [Quantity Billed] * [Item Rate] END

Anticipated Release Month (ifnull)

IFNULL([Anticipated Release Month],DATE(0))

====================================================================== Invoice History.twb — 0 calculated fields ======================================================================

====================================================================== Quantity History.twb — 7 calculated fields ======================================================================

Greater than Today

IF [Date] > TODAY() THEN 'Greater' Else 'Lesser' ENd

Combined Quantity

[Unfulfilled Sales (copy)_1124633302676783105]+[Fulfilled Sales (copy)_1124633302677065730]

New Date

Actual Ship Date (Date)

DATE([Actual Ship Date])

Anticipated Release Month (ifnull)

IFNULL([Anticipated Release Month],DATE(0))

Fulfilled Quantity

[Quantity Billed]

Unfulfilled Quantity

IF [Quantity Billed]> [Quantity] THEN 0 elseif [Status] = 'Closed' THEN 0 ELSEIF [Reorderable] = 'Discontinued' THEN 0 ELSE([Quantity] - [Quantity Billed]) END

====================================================================== Cancellation Report.twb — 105 calculated fields ======================================================================

% of Total CLOSED (local)

SUM(if [is_closed] = 1 THEN [net_amount] Else 0 END) / SUM([net_amount])

Avg Sales Order

sum( if year([Calculation_1852175773966282752]) = year(today()) THEN [net_amount] else NULL end)

(COUNTD( if year([Calculation_1852175773966282752]) = year(today()) THEN [entity_id] else NULL end)-1)

Previous Month Invoiced Revenue

IF MONTH(TODAY()) = 1 THEN IF MONTH([created_date]) = MONTH(DATEADD('month', -1, TODAY())) AND YEAR([created_date]) = YEAR(DATEADD('year', -1, TODAY())) THEN [net_amount] ELSE 0 END ELSE IF MONTH([created_date]) = MONTH(DATEADD('month', -1, TODAY())) AND YEAR([created_date]) = YEAR(TODAY()) THEN [net_amount] ELSE 0 END END

1*-Batik Booked Sales Previous Month

IF (MONTH([created_date]) = MONTH(DATEADD('month', -1, TODAY())) AND YEAR([created_date]) = YEAR(DATEADD('month', -1, TODAY()))) AND [class] IN ('Batik - Elementals', 'Batik - Collection', 'Batik - Hand Dyes', 'Batik - Other', 'Batik - Wideback', 'Precut Batik') THEN [net_amount] ELSE 0 END

1*-ME Booked Previous Month

IF month([Calculation_1852175773966282752])= MONTH(DATEADD('month', -1, TODAY())) AND year([Calculation_1852175773966282752])=YEAR(DATEADD('month', -1, TODAY())) AND [class] = 'Projects - ME' THEn [net_amount] ELSE 0 END

1*-Previous Month At Once

MONTH([Calculation_1852175773966282752])= month(dateadd('month',-1,today())) AND year([Calculation_1852175773966282752])=year(dateadd('month',-1,today())) AND ISNULL([anticipated_release_date]) THEN [net_amount] ELSEIF MONTH([Calculation_1852175773966282752])= month(dateadd('month',-1,today())) AND year([Calculation_1852175773966282752])=year(dateadd('month',-1,today())) and [Calculation_1852175773966282752] >= [anticipated_release_date] THEN [net_amount] ELSE 0 END

1-Two Months Ago Booked Revenue)

IF MONTH(TODAY()) = 1 THEN IF MONTH([created_date]) = MONTH(DATEADD('month', -2, TODAY())) AND YEAR([created_date]) = YEAR(DATEADD('year', -1, TODAY())) THEN [net_amount] ELSE 0 END ELSE IF MONTH([created_date]) = MONTH(DATEADD('month', -1, TODAY())) AND YEAR([created_date]) = YEAR(TODAY()) THEN [net_amount] ELSE 0 END END

1*-Previous Month Booked Revenue

1*-Previous Month Case Pack

If month([created_date])= month(dateadd('month',-1,today())) AND year([created_date])=year(dateadd('month',-1,today())) THEN [Case Packs (copy)_1825506009404993537] ELSE 0 END

1*-Previous Month Notions

If month([Calculation_1852175773966282752])= month(dateadd('month',-1,today())) AND year([Calculation_1852175773966282752])=YEAR(DATEADD('month', -1, TODAY())) THEN [Case Packs (qty) (copy)_4243587173808164865] ELSE 0 END

1-Prevoius Month Booked Revenue (copy)

If month([created_date])= month(today())-1 AND year([created_date])=year(today()) THEN [net_amount] ELSE 0 END

6 Month LTV Filter

2023 Invoiced

IF [type] = 'CustInvc' AND YEAR([created_date]) = 2023 THEN [net_amount] ELSEIF [type] = 'CashSale' AND YEAR([created_date]) = 2023 THEN [net_amount] END

2024 Invoiced

IF [type] = 'CustInvc' AND YEAR([created_date]) = 2024 THEN [net_amount] ELSEIF [type] = 'CashSale' AND YEAR([created_date]) = 2024 THEN [net_amount] END

2024 Invoiced_

IF [type] = 'CustInvc' AND YEAR([Calculation_1852175773966282752]) = 2024 THEN [net_amount] ELSEIF [type] = 'CashSale' AND YEAR([Calculation_1852175773966282752]) = 2024 THEN [net_amount] END

1*-Avg Sales Order

(sum( if year([Calculation_1852175773966282752]) = year(dateadd('month',-1,today())) THEN [net_amount] else NULL end)

(COUNTD( if year([Calculation_1852175773966282752]) = year(dateadd('month',-1,today())) THEN [entity_id] else NULL end)-1)) /MONTH(TODAY())

1-Batik Booked Sales Previous Month

At Once SO Based

Re-release Filter

IF ISNULL([re_release_date]) THEN 'In' ELSEIF [created_date] <= [re_release_date] THEN 'Out' else 'In' END

Last Sale Date

{fixed [rbd_collection]: max([created_date])}

Previous Month Booked Revenue

If month([created_date])= month(DATEADD('month',-1,today() )) AND year([created_date])=year(today()) THEN [net_amount] ELSE 0 END

YTD Revenue

if year(today()) = year([created_date]) THEN [net_amount] else 0 END

Sales Channel

monthName

DATENAME('month',[Calculation_1852175773966282752])

Case Packs Only

IF LEFT([rbd_item_id],2) = 'CP' THEN 'Case Pack' Else 'Other' END

Discount vs Full Price

Color Scheme

Price / Yd

SUM([net_amount]) / SUM([quantity])

Sales per SKU

SUM([net_amount]) /COUNTD([rbd_item_id])

Unique Customers

COUNTD([company_name])

First Order Tag

IF [created_date]=[Calculation_4168011138848129027] THEN 'First Order' else 'Subsequent Orders' END

AOV

sum([net_amount]) / COUNTD([tran_id])

Current Month Case Packs

If month([Calculation_1852175773966282752])= month(today()) AND year([Calculation_1852175773966282752])=year(today()) THEN [Case Packs (copy)_1825506009404993537] ELSE 0 END

Tran or Created Date

IF ISNULL([tran_date]) THEN [created_date] ELSE [tran_date] END

Up to Today

[tran_date] <= TODAY()

Booked Revenue

if [type]= 'SalesOrd' THEN [net_amount] else 0 END

Invoiced Revenue

IF [type] = 'CustInvc' THEN [net_amount] ELSEIF [type] = 'CashSale' THEN [net_amount] else 0 end

Unique Orders

COUNTD([tran_id])

YTD Orders

(COUNTD( if year([Calculation_1852175773966282752]) = year(today()) THEN [tran_id] else NULL end)-1)

Current Month Customers Sold

COUNTD(IF month([Calculation_1852175773966282752])= month(today()) AND year([Calculation_1852175773966282752])=year(today()) THEN [entity_id] ELSE NULL END)-1

Current Month Avg Sales per Shop

[Current Month Booked Revenue (copy)_248120232767164422] / [Calculation_248120232725712898]

Avg Booked Revenue (3 Month)

[Calculation_207376751944413187] / 3

Avg Invoiced Revenue (3 Month)

[Calculation_207376751944544260] / 3

ME Booked Current Month

IF month([Calculation_1852175773966282752])= month(today()) AND year([Calculation_1852175773966282752])=year(today()) AND [class] = 'Projects - ME' THEn [net_amount] ELSE 0 END

Last 7 Days

If [created_date] >= DATEADD('day',-8,today()) THEN [net_amount] else 0 END

Total Shops

COUNTD([entity_name])

Average Sales order

sum([net_amount]) / COUNTD([entity_name])

% of Total

sum([Calculation_3141331155803529217]) / sum([net_amount])

Batik Booked Sales Current Month

2022 Invoiced

IF [type] = 'CustInvc' AND YEAR([created_date]) = 2022 THEN [net_amount] ELSEIF [type] = 'CashSale' AND YEAR([created_date]) = 2022 THEN [net_amount] END

Current Month

IF MONTH([Calculation_1852175773966282752]) = MONTH(TODAY()) then 'in' END

Difference

SUM([sqlproxy.0pub43y0718nkx18avobm1wjjlx0].[value])- sum([2023 Invoiced (copy)_394909434306228226])

Percent of Target

sum([2023 Invoiced (copy)_394909434306228226])/SUM([sqlproxy.0pub43y0718nkx18avobm1wjjlx0].[value])

Days of Month

DAY(DATEADD('day',-1,DATEADD('month',1,DATETRUNC('month',TODAY()))) )

Percent of Month

DAY(TODAY())/[Calculation_394909434313089032]

Pre-Sales

IF [created_date]<[anticipated_release_date] then 'Presale' END

2023 Presales YTD

IF [created_date]<DATEADD('year',-1,TODAY()) AND YEAR([anticipated_release_date])=2023 then [net_amount] END

2024 Presales

IF [created_date]<[anticipated_release_date] AND YEAR([anticipated_release_date])=2024 then [net_amount] END

Sales Channels

IF entity_name = 'Fabric.Com' THEN 'Fabric.com' ELSEIF entity_name = 'Missouri Star Quilt Co, Inc.' THEN 'Missouri Star Quilt Co, Inc.' ELSEIF entity_name = 'Fat Quarter Shop' THEN 'Fat Quarter Shop' ELSEIF entity_name = 'Checker Notions Company Inc' THEN 'Checker' ELSEIF category = 'Big Box' THEN 'Big Box' ELSEIF category = 'Distributor 1' THEN 'International Distributors' ELSEIF category = 'Distributor 2' THEN 'International Distributors' ELSEIF [nt_employee_name] = 'Firth, Sammy M' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Gray, Lori' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Porter, Teresa' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Taylor, Jessica' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Goldenberg, Jeffrey P' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Ross, Chris' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Winninger, Jill' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Goeckeritz, Matt J' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Cloward, Bret C' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Mathis, Lisa' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Lindner, Jody' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Leonardo, Jody' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Miller, Mary' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Rurup, Gary' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Zimmerman, Cole' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Milligan, Angie' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Andersen, Gloria' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Herndon, Jami' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Deneault, Kim' THEN 'Independent Sales Reps' ELSEIF [nt_employee_name] = 'Hempfling, Felie' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Painter, Kristen' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Delaney, Scarlett' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Fogle, Chelsea' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Robinson, Gerri' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Freese, Angie' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Rushing, Kellie' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Maret, Anderson' THEN 'Independent Sales Reps' ELSEIF [nt_employee_name] = 'Eardley, Regina' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Statzer, Angela' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Barrera, Sarah' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Wu, Nellie' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Phippins, Robin' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Spratt, Wendy' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Grim, Nancy' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Dippiro, Christine' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'DiPirro, Christine' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Pike, Sara' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Dufur, Jennifer' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Mayer, Leisa' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Cartwright, Heather' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Anderson, Cindy' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Hince, Becky' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'Krueger, Cory' THEN 'RBD Direct Rep' ELSEIF [nt_employee_name] = 'In-House,' THEN 'In House' ELSE 'Independent Sales Reps' END

Sales Timing

If [created_date] < IFNULL([last_call_date],[anticipated_release_date]) THEN 'Pre-Sales' elseif [created_date] < DATEADD('day',10,[last_call_date]) THEN 'Pre-Sales' elseif [created_date] < [anticipated_release_date] THEN 'Last Call' ELSEIF DATEDIFF('day', [anticipated_release_date], [created_date]) <= 90 THEN '0-90 days post-release' ELSEIF DATEDIFF('day', [anticipated_release_date], [created_date]) <= 180 THEN '90-180 days post-release' ELSEIF [created_date] > [closeout_date] THEN 'Closeout' ELSE '+180 days post-release' END

Customer Age

END

First Order Date

{ FIXED [company_name]:MIN([created_date])}

% of Total CLOSED

SUM(if [is_closed 1] = 1 THEN [net_amount] Else 0 END) / SUM([net_amount])

At Once

IF [created_date]>[anticipated_release_date] THEN 'At Once' ELSE 'Presales' END

Is Big

Avg Customer Revenue

SUM([net_amount]) / COUNTD([company_name])

Buying Frequency

COUNTD([tran_id])/ COUNTD([company_name])

12 Month LTV Filter

IF DATEDIFF('day',[Calculation_4168011138848129027],today())<=365 THEN 'Out' elseif DATEDIFF('day',[Calculation_4168011138848129027],[Calculation_1852175773966282752])<=365 THEN 'In' Else 'Out' END

Combine Hand Dyes

IF FINDNTH([rbd_item_id],'-',2) > 0 THEN LEFT([rbd_item_id],FINDNTH([rbd_item_id],'-',2)-1) Else [rbd_item_id] END

Sales per Sales Order

SUM([net_amount]) / COUNTD([tran_id])

Prints

LEFT([rbd_item_id],FIND([rbd_item_id],'-')-1)

Main vs Secondary

IF CONTAINS([rbd_collection],'Express Yourself') THEN 'Main' elseif RIGHT([Calculation_6522901143033700359],1) = '0' THEN 'Main' Else 'Secondary' END

Colection Delta

{ FIXED [rbd_collection] : MAX({ FIXED [rbd_collection], [rbd_item_id] : SUM([net_amount]) }) } - { FIXED [rbd_collection] : MIN({ FIXED [rbd_collection], [rbd_item_id] : SUM([net_amount]) }) }

Cancelled Total

IF [is_closed 1] = 1 then [net_amount] else 0 end

Unfulfilled orders placeholder

[net_amount]

Fulfilled orders placeholder

[net_amount]/100

Date

[ship_date]

Unique SKUs

COUNTD([item_sku])

Percent of Year

DATEDIFF('day',#2024-01-01#,TODAY()) / 365

Percent of 2023 Presales

sum([Calculation_394909434918600717])/SUM([Calculation_394909434918600717 1])-1

Cancelled Total (local)

IF [is_closed] = 1 then [net_amount] else 0 end

Case Packs (qty)

Notions ($$)

If class = 'Notion RBD' THEN [net_amount] ELSEIF class = 'Other Notion' Then [net_amount] end

LIberty Sales

If [artist_1] = 'Liberty Fabrics' THEN [net_amount] else 0 END

Previous Month

IF MONTH([Calculation_1852175773966282752]) = Month(DATEADD('month',-1,TODAY())) THEN 'in' END

Previous Month At Once

1-Previous Month At Once

Current Month Booked Revenue (sum)

sum(If month([Calculation_1852175773966282752])= month(today()) AND year([Calculation_1852175773966282752])=year(today()) THEN [net_amount] ELSE 0 END)

1-Prevoius Month Booked Revenue

If month([created_date])= month(today())-1 AND year([created_date])=year(today()) THEN [net_amount] ELSE 0 END

Current Month Notions

If month([Calculation_1852175773966282752])= month(today()) AND year([Calculation_1852175773966282752])=year(today()) THEN [Case Packs (qty) (copy)_4243587173808164865] ELSE 0 END

1-Previous Month Case Pack

If month([created_date])= month(today())-1 AND year([created_date])=year(today()) THEN [Case Packs (copy)_1825506009404993537] ELSE 0 END

Current Month Liberty Sales

If month([Calculation_1852175773966282752])= month(today()) AND year([Calculation_1852175773966282752])=year(today()) THEN [Case Packs (qty) (copy)_553379871628591109] ELSE 0 END

1-Previous Month Notions

If month([created_date])= month(today())-1 AND year([created_date])=year(today()) THEN [Case Packs (qty) (copy)_4243587173808164865] ELSE 0 END

Current Month At Once

Discount vs Full Price (boolean)

IF [foreign_amount] = [net_amount] THEN 'Full Price' else 'Discount' END

1-ME Booked Previous Month

IF month([created_date])= month(today())-1 AND year([created_date])=year(today()) AND [class] = 'Projects - ME' THEn [net_amount] ELSE 0 END

Current Month Booked Revenue

If month([Calculation_1852175773966282752])= month(today()) AND year([Calculation_1852175773966282752])=year(today()) THEN [net_amount] ELSE 0 END

Previous Month Booked Revenue*

1-Previous Month Booked Revenue

If month([created_date])= month(DATEADD('month',-2,today() )) AND year([created_date])=year(today()) THEN [net_amount] ELSE 0 END

YTD Total Shops

if COUNTD( if year([Calculation_1852175773966282752]) = year(today()) THEN [entity_id] else NULL end)<= 1 THEN COUNTD( if year([Calculation_1852175773966282752]) = year(today()) THEN [entity_id] else NULL end)

else COUNTD( if year([Calculation_1852175773966282752]) = year(today()) THEN [entity_id] else NULL end) -1 END

YTD Previous Year Revenue*

if year(dateadd('year',-1,today())) = year([Calculation_1852175773966282752]) THEN [net_amount] else 0 END

YTD Revenue*

if year(today()) = year([Calculation_1852175773966282752]) THEN [net_amount] else 0 END

2024 Booked Revenue

IF [type] = 'SalesOrd' AND YEAR([Calculation_1852175773966282752]) = 2024 THEN [net_amount] END

1*-YTD Total Shops

COUNTD( if year([Calculation_1852175773966282752]) = year(dateadd('month',-1,today())) THEN [entity_name] else NULL end)

anticipated_release_month

DATE(DATETRUNC('month', [anticipated_release_date]))

====================================================================== Customer Universe.twb — 17 calculated fields ======================================================================

Customer Tiers

IF ISNULL([first_order_date]) THEN 'Legacy Registered' ELSEIF DATEDIFF('day',[first_order_date],TODAY())<=30 THEN 'New Customer' ELSEIF DATEDIFF('day',[most_recent_order_date],TODAY())<=123 THEN 'Active' ELSEIF DATEDIFF('day',[most_recent_order_date],TODAY())<=246 THEN 'Lapsed Tier 1(4-8 months)' ELSEIF DATEDIFF('day',[most_recent_order_date],TODAY())<=365 THEN 'Lapsed Tier 2(8-12 months)' ELSEIF DATEDIFF('day',[most_recent_order_date],TODAY())<=730 THEN 'Lapsed Tier 3(1-2 years)' ELSEIF DATEDIFF('day',[most_recent_order_date],TODAY())<=1095 THEN 'Lapsed Tier 4(2-3 years)' ELSEIF DATEDIFF('day',[most_recent_order_date],TODAY())>1095 THEN 'Lapsed Tier 5(3+ years)' ELSE 'OTHER' END

[Number of Records]

Order Type

IF [reship]=TRUE THEN 'Reship' ELSEIF [o_total_price]=0 AND CONTAINS([Calculation_2127387925884837888],'BLOCKSUB') THEN 'Regular' ELSEIF [o_total_price]=0 AND CONTAINS([Calculation_2127387925884837888],'BOM') THEN 'Regular' ELSEIF [o_total_price]=0 THEN 'Reship' ELSEIF [daily_deal]='true' AND [total_discount]>0 THEN 'Daily Deal' ELSEIF [on_sale]='true' THEN 'On Sale' ELSEIF [total_discount]>0 THEN 'On Sale' ELSE 'Regular' END

FirstOrderDateShopify

{ FIXED [o_customer_id]: min([Calculation_634163182157832194])}

item_tax_refunded_amount

[Calculation_587719813974208515]*[percent_of_subtotal]

SKU Prefix

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([sku],'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','')

Order - Item Count

{ FIXED [order_id]: count([product_id])}

TOR

[price]*[quantity] +[item_shipping_amount]

IFNULL([all_discounts] ,0) -IFNULL([Calculation_2127387930795425797],0) -IFNULL([line_item_refund_amount],0) +IFNULL([Calculation_179018093389676546],0) -IFNULL([item_order_refunded_amount],0)

gc_extended_price - gc_discounts

IF [gift_card] THEN [quantity]*[price]-[total_discount] ELSE 0 END

COGS

([quantity] - IFNULL([line_item_refund_quantity],0))*[item_cost]

Price - Discounts

[price] - [total_discount]

customer_created_at_CST

IF [customer_created_at]>=DATETIME("March 8, 2020 02:00:00") AND [customer_created_at]=DATETIME("March 14, 2021 02:00:00") AND [customer_created_at]=DATETIME("March 13, 2022 02:00:00") AND [customer_created_at]=DATETIME("March 12, 2023 02:00:00") AND [customer_created_at]=DATETIME("March 10, 2024 02:00:00") AND [customer_created_at]=DATETIME("March 9, 2025 02:00:00") AND [customer_created_at]=DATETIME("March 14, 2027 02:00:00") AND [customer_created_at]=DATETIME("March 12, 2028 02:00:00") AND [customer_created_at]=DATETIME("March 11, 2029 02:00:00") AND [customer_created_at]<DATETIME("November 4, 2029 02:00:00") THEN DATEADD('hour',-5,[customer_created_at]) ELSE DATEADD('hour',-6,[customer_created_at]) END

tax_refunded_amount

[refunded_amount]-([refunded_amount]/(1+ROUND([o_total_tax]/([o_total_price]-[o_total_tax]),4)))

Sales

[price]*[quantity]-[total_discount]-IFNULL([line_item_refund_amount],0)

Sales Type

IF [daily_deal]='true' AND [total_discount]>0 THEN 'Daily Deal' ELSEIF [on_sale]='true' THEN 'On Sale' ELSEIF [total_discount]>0 THEN 'On Sale' ELSE 'Regular' END

Quantity

IF [Calculation_2127387925884837888]='FBY' THEN ([quantity]-IFNULL([line_item_refund_quantity],0))/4 ELSEIF [Calculation_2127387925884837888]='TRIM' THEN ([quantity]-IFNULL([line_item_refund_quantity],0))/4 ELSEIF [Calculation_2127387925884837888]='QB' THEN ([quantity]-IFNULL([line_item_refund_quantity],0))/4 ELSEIF [sku]='MQ-ORDER' THEN 1 ELSEIF [sku]='MQ-PRIORITY' THEN 1 ELSE ([quantity]-IFNULL([line_item_refund_quantity],0)) END

Created At CST

IF [o_created_at]>=DATETIME("March 8, 2020 02:00:00") AND [o_created_at]=DATETIME("March 14, 2021 02:00:00") AND [o_created_at]=DATETIME("March 13, 2022 02:00:00") AND [o_created_at]=DATETIME("March 12, 2023 02:00:00") AND [o_created_at]=DATETIME("March 10, 2024 02:00:00") AND [o_created_at]=DATETIME("March 9, 2025 02:00:00") AND [o_created_at]=DATETIME("March 8, 2026 02:00:00") AND [o_created_at]=DATETIME("March 14, 2027 02:00:00") AND [o_created_at]=DATETIME("March 12, 2028 02:00:00") AND [o_created_at]=DATETIME("March 11, 2029 02:00:00") AND [o_created_at]<DATETIME("November 4, 2029 02:00:00") THEN DATEADD('hour',-5,[o_created_at]) ELSE DATEADD('hour',-6,[o_created_at]) END

====================================================================== Top Accounts.twb — 2 calculated fields ======================================================================

2024 Totals

IF year([created_date]) = 2024 then [net_amount] else 0 end

2023 Totals

IF year([created_date]) = 2023 then [net_amount] else 0 end

Source: ~/ai-projects/mission-control/plans/rbd-tableau-harvest.md