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