8 Replies Latest reply on Jan 29, 2018 8:56 PM by Zhouyi Zhang

# How to get MTD, QTD using Tableau LODs

Hi All,

How we can calculate MTD, QTD using Tableau LODs ? I'm using sample superstore excel sheet in Tableau Desktop version 10.2.2. Here the requirement is to get as on Date balance. for example, if i would like to get MTD Sales of Oct 20, data should show only for Oct 20 instead of sum of Oct 1 to 20 sales data. Also if i would like to see Dec 10 MTD sales data, it should show only Dec 10 MTD sales instead of sum of Sales from Dec 1-10. Same is the Logic for QTD also

If i would like to see QTD sales of Dec 10, data should be Sales data as on Dec 10 instead of sum of sales of Oct + Nov + Dec 1-10.

Basically what ever the date that we need MTD, QTD Sales, it should show sales data for that particular date. Could someone help on this pls. ?

Thanks,

PV

• ###### 1. Re: How to get MTD, QTD using Tableau LODs

Hi, Peevee

Not sure I understand your mtd, qtd logic, my understanding is there is no difference for these two, so you just need create exactly same two calculation for them as shown below.

A sample workbook attached.

ZZ

• ###### 2. Re: How to get MTD, QTD using Tableau LODs

Hi ZZ,

Many thanks for the Reply. I used your workbook and Retrieved data for ex. Oct 17, 2016 and MTD is showing 2953 for Furniture > Bookcases, but when I retrieved sales data for Oct 17, 2016 in a separate sheet for Furniture > Bookcases it showed 241.6 and I think it is correct. Is it because you've written formula considering Sales in the LOD instead of Order Date ? Also for ex. in case if Sales data is 0 on Oct 18, 2016, MTD & QTD also should show 0 instead of showing Oct 17 data. Hope you understand the scenario. In case if there is No Data, can we pass 0s for a particular date and write MTD, QTD calculations on top of that ? Could you help on this scenario of showing correct data ?

Thanks,

PV

• ###### 3. Re: How to get MTD, QTD using Tableau LODs

Hi, Peevee

In your case, if you have other dimension, you need add all your dimension used in the view to the calculation, e.g.

{Fixed [dimension 1], [dimension 2]... ,[dimension n]: sum([Sales])}

To your question, if there is no data in the data source, you can't generate a 0 because it is even not a null value, it is empty so that Tableau itself can't create something in the data for this case, unless, in your data source, you use a calendar table as primary and join your data to the calendar, which means there is a data row of that date and no matter your real data has sales or not.

ZZ

• ###### 4. Re: How to get MTD, QTD using Tableau LODs

Hi ZZ,

I've taken Sub category and it is showing the data same as Sales data. It is fine now and many thanks for the help. In this case, what ever the dimensions I consider in Result, I've to mention in LOD formula correct ?

I've a question here. You mentioned Calendar table as primary join to Calendar, are you referring in the superstore excel sheet or Database tables ? If it is in Excel sheet, Could you give some information on  what to consider for joining here ? Thanks in advance for your help

Thanks,

PV

• ###### 5. Re: How to get MTD, QTD using Tableau LODs

Hi

You can look online for date  dimension - you will find scripts to generate SQL tables or I think ready download file as well.

//J

• ###### 6. Re: How to get MTD, QTD using Tableau LODs

Hi ZZ,

In PnL (Revenue) scenario how we can calculate MTD, QTD ? For ex. to see MTD data on Oct 20, it should be the sum of data from Oct 1-20. In case of QTD, if I would like to see Dec 10 QTD, it should be MTD of Oct + MTD of Nov + MTD from Dec 1-10. Similarly for YTD, for ex. YTD of Dec 25 should be QTD of Q1 + QTD of Q2 + QTD of Q3 + MTD of Oct + MTD of Nov + Dec 1-25 MTD. Could you help on the calculation ?

Thanks,

PV

• ###### 7. Re: How to get MTD, QTD using Tableau LODs

Hi Jyothi,

You mean date dimension will convert to SQL query / Tables  ? If you don't mind, can you pls. provide the URL for the same

Thanks,

PV

• ###### 8. Re: How to get MTD, QTD using Tableau LODs

HI, Peevee

In this scenario, you need introduce a date parameter for date pick up, filter is not working for this instance.

ZZ