3 Replies Latest reply on Jan 15, 2016 12:54 AM by Ludo Koelman

    Calculated field

    Ajinkya Bhonsle

      Hi All,


      I have a filter called  FY_QUARTER which displays the quarter information of the forecast data eg: Q1FY2014,Q1FY2015,Q2FY2014,Q2FY2015,Q3FY2014,Q3FY2015,Q4FY2014,Q4FY2015, I have a calculated filed AS_OF_DATE(this filed is basically the date when our management forecasted the data) which has several dates like 4/3/2015, 4/17/2015,5/6/2015,5/14/2015,5/20/2015,6/20/2015 and so on. Our fiscal year starts from April. so Q1 - April,May,June, Q2 - July, August, September, Q3 - October, November, December, Q4- January,February,March. I want to create a mechanism where when a user selects for eg Q1FY2015 only the dates(AS_OF_DATE) that fall into that quarter pops up in the filter.

        • 1. Re: Calculated field
          Ludo Koelman

          Hi Ajinkya,


          If I understand correctly, there are 2 filters:

          1. Quarter filter

          2. Date filter (for example days)


          Then when you select one quarter in the first filter, you only want days within that quarter to be shown in the second one.


          If my interpretation is right, the option 'Only relevant values' is what you're looking for. Screenshots below.


          197264 - 2 filters.PNG

          197264 - only relevant.PNG


          Hope this answers your question



          • 2. Re: Calculated field
            Ajinkya Bhonsle

            Hi Ludo,


            Unfortunately that technique isnt working. For your better understanding: I am trying to compare Forecast vs Actual data. My forecast data comes from excel sheets and my Actual comes from oracle db.  I have used a Custom SQL to get the data from both the tables. My query is:


            select 'Q' || G.QUARTER_NUM  || 'FY' || G.PERIOD_YEAR AS FY_QUARTER, f.*

            from Weekly_v f, gl_periods g

            where PERIOD_SET_NAME = 'Accounting' and ENTERED_PERIOD_NAME != 'ADJ' and Quarter BETWEEN

            START_DATE and END_DATE

            UNION ALL

            select 'Q' || G.QUARTER_NUM  || 'FY' || G.PERIOD_YEAR AS FY_QUARTER, NULL as



            from Billings_V b, gl_periods g

            where PERIOD_SET_NAME = 'Accounting' and ENTERED_PERIOD_NAME != 'ADJ' and Invoice_date BETWEEN

            START_DATE and END_DATE


            the first part(before Union All) is the forecast data and the one below that is the Billings data(Actual).

            Table Definition:

            Weekly_v: - Forecast

            gl_periods: - GL Periods

            Billings_V: - Actuals


            When I pull in the data in Tableau. I have a filter called AS_OF_DATE and FY_QUARTER. To get back to my previous question. When the user selects the Quarter(eg:Q1FY14) I would like to display the dates(eg:4/3/2015, 4/17/2015,5/6/2015, etc..) that forecast only for data Q1FY14.


            I hope you got my question.

            • 3. Re: Calculated field
              Ludo Koelman

              Hi Ajinkya,

              I'm not completely sure I understand the situation, screenshots or a demo workbook would help.


              But I'll give it a try.


              So there's more than one data connection, coming from different sources. You use a custom SQL UNION statement to get all the data into one connection. I see three problems here:

              1. The 2 different data sources have different date granularity: forecast has a line per quarter, the actuals are per day

              2. Because you union the 2 sources, the data for forecast and actuals are not in the same rows.

              3. the date field you use as a filter is not used for both input sources



              Date forecastvalue forecastDate actualvalue actual


              Does the above look like your data structure? If so, you can't focus on the actuals date, because you'd be filtering out all forecast data and vice versa. In that case, I see two options:


              1. Use a blend instead of a union. This is easier to set up, refreshes more quickly and provides certain flexibility

              2. Use a parameter to filter from. The selected value filters both date fields through a calculated field with an OR statement, something like:


              DATEPART('quarter',[Date forecast]) = Parameter


              DATEPART('quarter',[Date actual]) = Parameter


              Then you can use this field as a filter.


              If this doesn't help, please provide a sample workbook