Tableau - Sum week-to-date, month-to-date, year-to-date, etc

Question

I am working on a simple dashboard that will show Daily, Week to Date, Month to Date, and Year to Date totals for a product. My underlying data is a list of sales including the date they occurred and the total amount of the sale. I am using the following calculated field to create buckets for the dates

IF [Date] = TODAY() THEN "Today"
ELSEIF DATETRUNC('week', [Date]-1)+1 <= TODAY()
   AND DATEDIFF('week',DATETRUNC('week', [Date]-1)+1, TODAY()) = 0
   AND MONTH([Date]) = MONTH(TODAY()) 
   AND YEAR([Date]) = YEAR(TODAY()) THEN "WTD"
ELSEIF MONTH([Date]) = MONTH(TODAY()) 
   AND YEAR([Date]) = YEAR(TODAY()) THEN "MTD"
ELSEIF YEAR([Date]) = YEAR(TODAY()) THEN "YTD" END

My problem is that this doesn't let me sum the buckets so that MTD includes the WTD values, etc. I've looked at a similar SO post but that solution isn't what I am trying to accomplish. Rather than seeing the values for each day I want a small table that just shows totals for each category, e.g.

  VOLUME
Daily ####
WTD   ####
MTD   ####
YTD   ####

mzunhammer mzunhammer · Accepted Answer · 2019-09-18T16:07:24

You "create calculated fields" based on [Date] named "[WTD]" "[MTD]" "[YTD]" that encode True/False (or equivalently 0/1). Just splice your code above in separate fields. You then create calculated measures by multiply these with the outcome of choice (e.g. [WTD]*[Sales in €]). Then you pull "Measure Values" to "Text" and only keep the created measures.

Tableau - Sum week-to-date, month-to-date, year-to-date, etc

1 Answers