Calculate Sale Frequency
Attached below Sample data. The database table has location and sale date fields. I need to calculate Sale frequency report, which mentions if a particular location has a Weekly sale, biweekly or monthly sale wrt to the Sale date. Appreciate your help on how to calculate this in Domo?
Thank you
Best Answers

Something like this MIGHT work, but it's hard to get it exactly correct without playing with the data directly.
If the sales/days are consistent, I think you can look back at the previous month's counts to figure out the frequency. This is an example, not sure if it will exactly work, but what it's doing is counting up the distinct days that had a transaction in the previous month, grouping by Location and day of the week, then using those counts to decide the frequency.
case when SUM(COUNT(DISTINCT case when SUBDATE(DATE_FORMAT(CURRENT_DATE(), '%Y%m01'), interval 1 month) = DATE_FORMAT(
transaction_date
, '%Y%m01') thentransaction_date
end) fixed (by DAYNAME(transaction_date
),Location
)) = 1 then 'Monthly'
when SUM(COUNT(DISTINCT case when SUBDATE(DATE_FORMAT(CURRENT_DATE(), '%Y%m01'), interval 1 month) = DATE_FORMAT(transaction_date
, '%Y%m01') thentransaction_date
end) fixed (by DAYNAME(transaction_date
),Location
)) > 3 then 'Weekly'
else 'BiWeekly'
end0 
I might try something like creating a Magic ETL and use location and trans date to sort rows in ascending order. Add a formula and find the location date differences. Calculate the averages. Then have some kind of formula to classify the sale frequency.
CASE
WHEN Avg_Date_Diff <= 7 THEN 'Weekly'
WHEN Avg_Date_Diff <= 14 THEN 'BiWeekly'
ELSE 'Monthly'
END** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Answers

I'm not totally clear on what you're trying to do. Are you trying to create the "Frequency" field you circled, or do you want to translate it somehow? What are you hoping to display in your card?
0 
Yes @Sean_Tully . I need to calculate the "Frequency" field in Domo. I will need to check the Sale transaction to determine this. I tried using below calculation
case when DateDiff(Current Date, Max(Trans Date) ⇐ 7) then 'Weekly'
when DateDiff(Current Date, Max(Trans Date) ⇐ 14) then 'BiWeekly'
Else 'Monthly'
End
But this does seem to work for me for all cases. So wondering what is best way to calculate this.
Thank you for looking into it.
0 
Something like this MIGHT work, but it's hard to get it exactly correct without playing with the data directly.
If the sales/days are consistent, I think you can look back at the previous month's counts to figure out the frequency. This is an example, not sure if it will exactly work, but what it's doing is counting up the distinct days that had a transaction in the previous month, grouping by Location and day of the week, then using those counts to decide the frequency.
case when SUM(COUNT(DISTINCT case when SUBDATE(DATE_FORMAT(CURRENT_DATE(), '%Y%m01'), interval 1 month) = DATE_FORMAT(
transaction_date
, '%Y%m01') thentransaction_date
end) fixed (by DAYNAME(transaction_date
),Location
)) = 1 then 'Monthly'
when SUM(COUNT(DISTINCT case when SUBDATE(DATE_FORMAT(CURRENT_DATE(), '%Y%m01'), interval 1 month) = DATE_FORMAT(transaction_date
, '%Y%m01') thentransaction_date
end) fixed (by DAYNAME(transaction_date
),Location
)) > 3 then 'Weekly'
else 'BiWeekly'
end0 
I might try something like creating a Magic ETL and use location and trans date to sort rows in ascending order. Add a formula and find the location date differences. Calculate the averages. Then have some kind of formula to classify the sale frequency.
CASE
WHEN Avg_Date_Diff <= 7 THEN 'Weekly'
WHEN Avg_Date_Diff <= 14 THEN 'BiWeekly'
ELSE 'Monthly'
END** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Categories
 All Categories
 1.7K Product Ideas
 1.7K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 294 Workbench
 6 Cloud Amplifier
 8 Federated
 2.8K Transform
 97 SQL DataFlows
 607 Datasets
 2.1K Magic ETL
 3.8K Visualize
 2.4K Charting
 707 Beast Mode
 49 App Studio
 39 Variables
 667 Automate
 170 Apps
 446 APIs & Domo Developer
 44 Workflows
 7 DomoAI
 33 Predict
 13 Jupyter Workspaces
 20 R & Python Tiles
 391 Distribute
 111 Domo Everywhere
 274 Scheduled Reports
 6 Software Integrations
 115 Manage
 112 Governance & Security
 Domo Community Gallery
 31 Product Releases
 9 Domo University
 5.3K Community Forums
 40 Getting Started
 30 Community Member Introductions
 103 Community Announcements
 4.8K Archive