3 Replies Latest reply: Jul 21, 2013 3:32 PM by Kent Culpepper RSS

    Question w Distinct Count Aggregations

      Im not observing the expected result of an aggregation of a distinct count. See the below screenshot;

      4-7-2013 2-37-46 PM.png

      I am expecting a value of 4.

       

      The expression is the following;

      =

      if(

      (
      sum(if(vYear-1=Year and vYear_Week=Year_Week,Sales_Units,0))+

      sum(if(vYear-1=Year and vmaxVYrWk=Year_Week,Ending_Inventory_Units,0))>0)

      ,
      count(DISTINCT(Location_Id))

      )

       

      Using a Total Mode = Sum will return a value of 4; however there are several other product/ calender dimensions in this chart-- and a "sum" does not appear to work when exposing them.

      Also, the chart can be changed to a pivot table on-demand; so this aggregation must also support an approach that does not include a different "total mode".

       

      Thanks.

        • Re: Question w Distinct Count Aggregations

          i belive i may have solved.. basic problem with expression;

           

          =

          if(

          (
          sum(if(vYear-1=Year and vYear_Week=Year_Week,Sales_Units,0))+

          sum(if(vYear-1=Year and vmaxVYrWk=Year_Week,Ending_Inventory_Units,0))>0)

          And

          ,
          count(DISTINCT(if(vYear-1=Year and vYear_Week=Year_Week,Location_Id)))

          )

           

          • Re: Question w Distinct Count Aggregations
            Stefan Wühl

            You are getting the Expression Total and I think it's calculated right be QV.

             

            To only count the location ids where condition is fulfilled, you can try something like:

             

            count(

            {<Location_Id = {"=sum( {<Year = {$(=vYear-1)}, Year_Week= {$(vYear_Week)} >} Sales_Units)+sum({<Year = {$(=vYear-1)}, Year_Week = {$(vmaxVYrWk)} >} Ending_Inventory_Units) > 0"} >}

            DISTINCT Location_Id)

              • Re: Question w Distinct Count Aggregations

                Hi

                 

                i used your code but something strange is occurring. so the following is what i am using as my expression- i added another condition as i do not want to count DC/ warehouse locations;

                 

                count

                ({<
                Location_Id =
                {
                "=sum( {<Year = {$(=vYear)}, Year_Week= {$(=vYear_Week)} >} Sales_Units)
                +sum({<Year = {$(=vYear)}, Year_Week = {$(=vmaxVYrWk)} >} Jeeves_Ending_Inventory_Units) > 0 And Location_Type <> 'DC'"
                }
                >}
                DISTINCT Location_Id)

                 

                so i only want to count locations that have sales or ending inventory and are not a DC-- for that time period specified (last week)

                 

                so when i have selected a product it calculates perfectly as shown below

                 

                7-21-2013 3-27-46 PM.png

                however when i dont have a product selected it doesnt seem to calculate as expected as shown below;

                7-21-2013 3-30-07 PM.png

                any ideas?

                 

                thanks!