Calculating YTD MTD in Tableau with fixed LOD (level of detail)

0
votes

Trying to make a report in Tableau that would show the following sales stats:

  • sales yesterday
  • sales week-to-date
  • sales month-to-date
  • sales run-rate (30 days) - based on month-to-date calculation

The problem is that the data source is Shopware DB and it stores all historical states of an order: when it was created, when it was paid but not shipped, when it was shipped, when it was closed. I.e. duplicate orders.

The report is connected directly to Shopware DB without data prep. Thus, to remove the duplicate entries for each order state I am using a FIXED level of detail calculation to calclate the correct SUM:

{Fixed [Order Number],[identifier]:MIN([total_price])}

However, when I am trying to use either of solutions for MTD, YTD calculations that I found online based on this fixed calculated field "Correct SUM", I am getting totally strange numbers, which i am not even able to understand (they are much more then what I have, for example, in Power BI for the same data source; 17k USD instead of 238 USD, 65k USD instead of 55k USD, etc.).

I tried using the following calculated fields:

  1. SUM IF: SUM( IF MONTH([Order Date])=MONTH(TODAY()) THEN [Correct SUM] END

  2. True/False calculated field used as a filter (as described here): [Order Date] <= TODAY() AND DATETRUNC( "month", [Order Date]) = DATETRUNC("month", TODAY())

Can anyone advise what to do next?

1
tableau-api
Why not you use the relative dates feature of Tableau? ![Preview](i.stack.imgur.com/XmAcW.png) – AnilGoyal
Looks like I won't be able to put several numbers into 1 table, right? YTD, MTD and yesterday. But thanks for the tip anyway. Will have to use it unless I find a way with calculations. – Джон
In first LOD field you are taking MIN of total_price. Does that mean your price varies or for sake of aggregation have you taken it? – AnilGoyal
can you give us some sample rows to work upon? I think you should remove [identifier] from level of detail in the first calculated field – AnilGoyal
well, unfortunately, i can't provide a sample. actually, i took the LOD formula 'as is' and only now thought about that MIN issue. but as far as i am concerned the total_price filed doesn't change in this DB. thanks for the suggestion regarding identifier, i will try without it – Джон

1 Answers

0
votes

You should create the field MTD_ Sales: IIF (year ([order date] = year (today()) and month ([order date] = year (today()) and [order date] < = today(), 1,0); first, set the field MTD_sales becomes a dimension, and then put this field into the filter of tableau. Select "1". Then you can select the dimension and value you want, MTD_ Sales refers to the date when the MTD is met. Similarly, YTD does the same