Calculate Sale Frequency

A2024

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

Sean_Tully

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') then transaction_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') then transaction_date end) fixed (by DAYNAME(transaction_date), Location)) > 3 then 'Weekly'
else 'Bi-Weekly'
end

ArborRose

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

Sean_Tully

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?

A2024

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.

Sean_Tully

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') then transaction_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') then transaction_date end) fixed (by DAYNAME(transaction_date), Location)) > 3 then 'Weekly'
else 'Bi-Weekly'
end

ArborRose

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