Formula to compare sales per month, depending on days through current month

Hope you can help...

I have a measure called 'Working Days Through Current Period' which does exactly as it says (Period = month). I want to be able to use this measure to compare how the sales in the current period (for example 4 working days into March) compare to previous periods (against 4 days sales into February or January for example).

I have another measure which shows working days through Period, which I think I need to use for this (as we are through January and February, this shows the maximum number of days for those months, but 4 in March per my above example), but I am unsure how to create a formula to calculate this.

I assuming a cumulative_sum is required?

Any help would be much appreciated!

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Sam,

     

    Can you please post your formulas here? It would help understand the logic that you are currently using.

     

    I believe you should be able to use day_number_of_ functions to get the comparison of in the current period vs. previous periods. Alternatively, you could do a formula for Cumulative Sales = cumulative_sum(sales, date) and use a search as follows:
    "Cumulative Sales" "Daily" "Month of Year" "This Year"

    Like 1
  • Hey Sam Greenwood ,

    Yes, per Gautam, seeing your formula would be helpful.  You can use the cumulative_sum and then keywords this month versus last month month to date. 

    Thanks,

    Rachel

    Like 1
  • For example, you could use use the 'is_weekend()' keyword to select only weekdays for your sum as well.  

    e.g. sum_if ( not is_weekend(date_field) , field_to_sum) 

    will sum all days M-F.  If you have different business days, you may consider uploading a custom calendar https://docs.thoughtspot.com/6.0/admin/setup/set-custom-calendar.html

    Like 1
Like Follow
  • Status Answered
  • 2 mths agoLast active
  • 3Replies
  • 37Views
  • 5 Following