Calculate Sale Frequency

A2024
A2024 Member
in Beast Mode

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?

image.png

Thank you

Tagged:

Best Answers

  • Sean_Tully
    Sean_Tully Contributor
    Answer ✓

    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
    ArborRose Coach
    Answer ✓

    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! **

Answers

  • Sean_Tully
    Sean_Tully Contributor

    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
    A2024 Member

    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
    Sean_Tully Contributor
    Answer ✓

    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
    ArborRose Coach
    Answer ✓

    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! **

Categories