3 Replies Latest reply on Nov 18, 2019 6:28 PM by Jennifer VonHagel

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

    Myriam Lochu



      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')



      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.