Search code examples
tableau-apicrosstabcalculated-field

Quarter calculation showing multiple month

I have four crosstabs, the first three are the 1st, 2nd & 3rd months that make up a Quarter. The fourth crosstab reflects the total of that Quarter all together. I have the ‘Month’ Dimension filtering on QUARTER(month) for the fourth crosstab.

What I would like is to have a calculated field or parameter that controls the months displayed in the first three tables dependent on what Quarter is selected.

For instance, Q1 2018 for crosstab #4 and the first three crosstabs show Jan-18, Feb-8 & Mar-18. Or when the Q3 2017 is selected then Jul-17, Aug-17 & Sept-17 is shown.

Solution

    1. Apply the QUARTER(Month) filter from the sheet to the individual month sheets as well. This will filter all sheets to the chosen quarter.

    2. Create calculated fields to filter the individual month sheets to single months. a. Create a new calculated field. Name the field "!Month 1 Filter", insert the following expression, and click Ok.

    DATEDIFF('month',DATETRUNC('quarter',[Month]),DATETRUNC('month',[Month]))=0

    b. Create a new calculated field. Name the field "!Month 2 Filter", insert the following expression, and click Ok.

    DATEDIFF('month',DATETRUNC('quarter',[Month]),DATETRUNC('month',[Month]))=1

    c. Create a new calculated field. Name the field "!Month 3 Filter", insert the following expression, and click Ok.

    DATEDIFF('month',DATETRUNC('quarter',[Month]),DATETRUNC('month',[Month]))=2

    1. Filter each month sheet with its respective filter.