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, bi-weekly 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-%m-01'), interval 1 month) = DATE_FORMAT(
transaction_date
, '%Y-%m-01') thentransaction_date
end) fixed (by DAYNAME(transaction_date
),Location
)) = 1 then 'Monthly'
when SUM(COUNT(DISTINCT case when SUBDATE(DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01'), interval 1 month) = DATE_FORMAT(transaction_date
, '%Y-%m-01') thentransaction_date
end) fixed (by DAYNAME(transaction_date
),Location
)) > 3 then 'Weekly'
else 'Bi-Weekly'
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 'Bi-Weekly'
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 'Bi-Weekly'
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-%m-01'), interval 1 month) = DATE_FORMAT(
transaction_date
, '%Y-%m-01') thentransaction_date
end) fixed (by DAYNAME(transaction_date
),Location
)) = 1 then 'Monthly'
when SUM(COUNT(DISTINCT case when SUBDATE(DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01'), interval 1 month) = DATE_FORMAT(transaction_date
, '%Y-%m-01') thentransaction_date
end) fixed (by DAYNAME(transaction_date
),Location
)) > 3 then 'Weekly'
else 'Bi-Weekly'
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 'Bi-Weekly'
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.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive