3 Replies Latest reply on Feb 23, 2012 6:41 AM by Shawn Wallwork

# Generating Quarter to Date and Year to Date data.

Hi experts,

I have been trying to get the answer for way too long, so I decided to come here for help.  I have a column of daily sales number and quarter id number and date and most of the relevant information.  How do I have tableau generate daily Quarter to date sales total (the total sale number from the begining of the quarter to today)?  I figure I need to create some variable and do some calculation to achieve that..but I dont know how.

Thank you very much

• ###### 1. Re: Generating Quarter to Date and Year to Date data.

Hi Charlton, there are a lot of different ways of going about doing this. Which option you choose will depend on what else you want in your chart, what else is being filtered, and many other factors. But having said that here's a basic formula for producing a quarter to date sales figure:

IIF(DATEPART('quarter',[Order Date])= DATEPART('quarter',TODAY()),  [Sales],  NULL)

This makes the assumption that your view is filtered on the current year, otherwise you'll get data from the first quarter of all years in the patrician. Like I said there are going to be many factors in choosing the best way to go about this, so if you could post a sample packaged workbook we'll be able to better help you.

--Shawn

• ###### 2. Re: Generating Quarter to Date and Year to Date data.

Thanks a lot for the response. Shawn.  It's super helpful and this works.,I was able to filter out different year with another IIF statement.  I'm going to try it some more.  Will come back if I run into problems.

Actually, I also have a un-related question that does tableau support any function like distinct?  There are some duplicates in my data for example, for the same date and same region, the value in total month sales is the same.  But of course if I try to plot that in tableau, all those duplicates will be included.  is there any way to eliminate that problem?

Thanks much!

• ###### 3. Re: Generating Quarter to Date and Year to Date data.

There's COUNTD() but that isn't going to work for what you're doing. I suspect there are some contortions you could do using calculated fields (I've never tried this), but it would probably be easier to take care of the duplicates in the data source. If you want to try the calculated field route, then you should post this as another question and see if someone has tried this before. Good luck.

--Shawn