1 Reply Latest reply on Nov 9, 2019 12:06 PM by Jennifer VonHagel

    Sort a field by a date field with parameter control - duplicated lines

    Myriam Lochu

      Hi

       

      I built a Gantt chart with Tableau and will build a dashboard out of it, and the most important date is called the FCS (or FDW, same meaning).

      However the FCS date is not known for all projects, at a given time. So I have null values.

       

      I created a field called "FCS date-sort" :

       

      if ([Type] == 'Milestone') AND CONTAINS([newtitle], "FDW")

      then [End Date]

      else DATE('12312018')

      end

       

      where I replaced the null value with a value long past (12/31/2018)

       

      When I sort 'Project name' by this field (with Aggregation = Max), I get the sorting I expect for my 8 projects : 

       

       

      The first 4 projects are sorted according to their FCS dates, then come the projects that had no FCS date.

       

      Now I want to give the user the ability to choose Ascending or descending order.

       

      I created :

      - a parameter (Ascending/Descending) and added the parameter control

      - a new field, called Sort By FCS :

      DATEDIFF('day',TODAY(),[FCS date-sort])*IIF([Ascending/Descending]='Ascending',1,-1)

       

      When I add this field as a row, suddenly I get 4 extra lines : projects for which I had a FCS date are duplicated.

       

       

      What am I doing wrong ?

      How can I achieve sorting of Project name by FCS date through parameter control for my users ?

       

      Thanks if you can help me.

       

      Myriam