9 Replies Latest reply: May 20, 2017 7:58 AM by ar eiuiew RSS

    Counting only the last distinct value

    Matthias Walter

      Hi everybody,

      I created a Dashboard in Qlik Sense with a Pivotchart. The Pivotchart has as rows the Dates (a Drilldown Month-Date) and as the colums the Projects. The key figure is a basic "count(distinct [Task #])" and this works (mostly) fine. My problem is, since every project has multiple Task #, and every Task # can exist multiple times and on multiple dates, the count is only correct if every instance of the Task # is in the same Dateclass (e.g. in March). If I go a level deeper (Date) the count of Task # increases because the instances aren't in the same Dateclass anymore.

      Is it possible to count only the last instance of the Task #?

      The Raw Data looks like this:

         

         

      DateTask #Project
      02.03.2017697273Project1
      03.03.2017697273Project1
      03.03.2017697069Project1
      03.03.2017697069Project1
      03.03.2017697294Project1
      03.03.2017697186

      Project1

       

      As you see, if the Dateclass is "March", there are 4 distinct Task #, but if its on a Date level its 1 on the 02.03. and 4 on the 03.03. I want to count only the last one, so 0 on the 02.03. and 4 on the 03.03. so that it sums up correctly.

       

      Thank you in advance for your help!