CODEWITHSUNDEEP
tableau-api
Джон
Джон

Reputation: 38

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

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

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?

Upvotes: 0

Views: 2009

Answers (1)

tamerlin
tamerlin

Reputation: 1

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

Upvotes: 0

Related Questions