2 Replies Latest reply: Oct 13, 2017 1:54 PM by Afroz Hyder RSS

    Pivot Showing Greater Number When Unfiltered

    Afroz Hyder

      I have this data set where I want to count accounts that had more than one device to them. When the chart is unfiltered I get a higher number, when I filter it to the month it should the correct answer. Can someone help please.

       

      SCRIPT:

       

      LOAD

          MonthYear,

          Account,

          Device,

          LOG_DATE

      FROM [lib://AttachedFiles/Qlik Community.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

       

      DIMENSION:

      MonthYear

       

      EXPRESSION:

      aggr(count({< Account = {"=Count(distinct Device) > 1"} >} DISTINCT Account), MonthYear)

       

       

      Chart Shows this:

      WrongAnswer.png

      When I filter to individual month i get the right answer.

      RightAnswer.png

       

      Any ideas?

       

      I want the calculation to be done on the first end (in pivot table)