7 Replies Latest reply on May 15, 2017 2:27 PM by Yuriy Fal

    How to filter NULL record from the calculated field output which is used in the next calculated field.

    varsh goel

      I am new to Tableau and working on a requirement where I have a table as below with Fiscal year, Period_Number, Resource Start Date and Period Start and End Date.

      Requirement - To have two Filters in the Dashboard for Fiscal Year and Period Number. For the selected Fiscal Year and Period Number I have to get the PERIOD_END_DT and then count the Resource Name where START_DATE < PERIOD_END_DT.

      Example - If the selected value is P11 and 2017 then PERIOD_END_DT for this will be 4/1/2017. Further needs to have START_DT < PERIOD_END_DT (in this case 4/1/2017) so this should result the count as 5.

      Existing Code - I tried implementing this by below calculations but its returning the count only for the selected Period i.e. 1 in this case instead of count as 5 because Calculation 1 iss returning null.

      Calculation 1:

      If [PERIOD_NO]=[Parameters].[PERIOD_NO] and [FISCAL_YEAR]=[Parameters].[FISCAL_YEAR]

      then  [PERIOD_END_DT] END                         -> Issue is that its returning Null and PERIOD_END_DT. NULL is creating issue.

      Calculation 2:

      IF date([START_DATE]) <=  [CAL_Headcount_Period_End] THEN "TRUE" ELSE "FALSE" END              -> Placing this calculated field in Filter Shelf to display only TRUE   records.


      Really appreciate your help.


      Resource NamePeriod NumberFiscal YearSTART_DATEPERIOD_START_DTPERIOD_END_DT
      XP7201712/1/2016 0:0011/13/2016 0:0012/10/2016 0:00
      YP820171/1/2017 0:0012/11/2016 0:001/7/2017 0:00
      ZP920172/1/2017 0:001/8/2017 0:002/4/2017 0:00
      AP1020172/10/2017 0:002/5/2017 0:003/4/2017 0:00
      BP1120173/20/2017 0:003/5/2017 0:004/1/2017 0:00