5 Replies Latest reply: Jan 4, 2017 5:11 PM by Deborah Biardo RSS

    Get count distinct if max value in Pivot Table

    Deborah Biardo

      Hi,

      Please, I need help with this problem.

      I have a dataset where each row is a customer per month. The fields are: name (of  customer-Empresa), month/year (MESAÑO) and Status(IdEstatus - containing four values: 1 -active, 2 - at risk, 3 - loss, 4 inactive ).

      I need to show on a pivot table the quatity of distinct customers with status=2 (1,2,3,4) by month and quarter. For each row of the pivot table (month or quarter) I need the count of different customers if the max(Status)=2 (in that period)

       

      The pivot table should look something like this (all numbers are drawn from the dataset)

      I have tried:

       

      IF(ISNULL(aggr (MAX((IdEstatus)),[MESAÑO])), aggr (MAX((IdEstatus)),Tri),aggr (MAX((IdEstatus)),[MESAÑO])).

       

      but when I try to count this it does not work.

       

      Other approach was:

       

      COUNT(DISTINCT IF(aggr(NODISTINCT max(IdEstatus),EMPRESA)=2,EMPRESA)) but gives me all zeros


      Quarter

      Month/Year

      Count of Customers with Status=2

      2

      0

      06/2016

      0

      3

      11

      07/2016

      2

      08/2016

      7

      09/2016

      9

      4

      12

      10/2016

      8

      11/2016

      8

       

      Thanks for your help !!!