3 Replies Latest reply on Apr 10, 2018 3:42 AM by Zhouyi Zhang

# Find YTD sales and Previous year YTD sales by calculated fields (without parameters)

Hi everyone,

I have sales data for 3 years starting from Jan' 16 to Mar'18. I would like to calculate YTD and previous year YTD (marking as PYTD) sales by using calculated fields and without parameters.

For example, YTD sales will be calculated for sum of sales from Jan'18 to Mar'18 and PYTD sales will be sum of sales from Jan'17 to Mar'17.

I have got the following formulas for both YTD and PYTD which contains a today function used in it and it throws an error in results. I tried to use MAX(Date) function to find latest date available in data, but it throws an error message saying "cannot mix aggregate and non-aggregate arguments with this function"

YTD

IF

DATEDIFF('year',[Date],TODAY())= 0

AND

[Date] <= DATETRUNC('day',([Date]))

THEN [Value] END

PTYD

IF

(

YEAR([Date]) = YEAR(TODAY())-1

AND

(MONTH([Date]) <= MONTH(TODAY())

AND

DAY([Date]) <= DAY(TODAY()))

)

THEN [Value]

END

The error facing in PYTD formula, MONTH(TODAY()) part looks for today's date and it calculates sales for previous year's 4 months instead of 3 months. Sales is available up to Mar'18.

Ideally, when three months sales are calculated from current year, the sales for same three months should be calculated from previous year.

Also, when a new month sales get added to the source, it should be applicable to new month from current year and corresponding previous year.

Attached a packaged workbook (Tableau desktop version 10.5) having a sheet calculating YTD and PYTD.

Can someone sort this out immediately.

Any help would be great.

Thanks,

Jyothsana

• ###### 1. Re: Find YTD sales and Previous year YTD sales by calculated fields (without parameters)

Hi Jyothsana,

Thanks,

Veeru

• ###### 3. Re: Find YTD sales and Previous year YTD sales by calculated fields (without parameters)

Hi, Jyothsana

Please find my solution attached as well as screenshot below as reference.

Hope this helps

ZZ