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

           

          Ludo

          • 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

            AS_OF_DATE,SOLD_TO_CUSTOMER, NULL, PART_NUMBER, ITEM_FAMILY, NULL, NULL, TO_DATE

            (QUARTER_START_DATE,'DD-MON-RRRR'), SHIPPING_QUANTITY/1000 AS SHIP_QTY  , NULL, LINE_AMOUNT/1000

            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
              01/01/2015100
              01/04/2015110
              01/07/2015120
              01/10/2015130
              01/01/2016140
              01/01/20152
              02/01/20153
              03/01/20154
              04/01/20155
              05/01/20156
              06/01/20157
              07/01/20158

               

              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

              OR

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

               

              Then you can use this field as a filter.

               

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

               

              Ludo