13 Replies Latest reply: Sep 3, 2013 9:18 AM by Steve Zagzebski RSS

    Aggr Count Distinct

    Steve Zagzebski

      I need to count distinct clients who had a combined 100 in sales. Looking for some help on expressions:\

       

      DATA:
      DeptClientSales
      A150Doesn't Meet Criteria
      A140Doesn't Meet Criteria
      A275Meets Criteria
      A275Meets Criteria
      A3150Meets Criteria
      B1100Meets Criteria
      B1100Meets Criteria
      B450Doesn't Meet Criteria
      C1150Meets Criteria
      C5100Meets Criteria
      C5100Meets Criteria
      OBJECT:
      DeptTotal Sales of Clients who had at least 100 in salesCount Distinct Clients with Total Sales of 100 or more
      A3002
      B2001
      C3501
        • Re: Aggr Count Distinct
          Stefan Wühl

          Maybe like attached?

          • Re: Aggr Count Distinct
            Stefan Wühl

            There is one...

             

            Have you looked only your inbox? I think it won't show attachments there. Follow the link to the full discussion.

             

            [edit:

             

            If you can't open it:

             

            I added a field in the script:

            LOAD *,

                 AutoNumber(Dept&Client) as DeptClient

            INLINE [

            Dept,    Client,    Sales

            A,    1,    50,    Doesn't Meet Criteria

            A,    1,    40,    Doesn't Meet Criteria

            A,    2,    75,    Meets Criteria

            A,    2,    75,    Meets Criteria

            A,    3,    150,    Meets Criteria

            B,    1,    100,    Meets Criteria

            B,    1,    100,    Meets Criteria

            B,    4,    50,    Doesn't Meet Criteria

            C,    1,    150,    Meets Criteria

            C,    5,    100,    Meets Criteria

            C,    5,    100,    Meets Criteria

            ];

             

            then created a chart with dimension Dept and two expressions:

             

            =sum({<DeptClient = {"=sum(Sales)>=100"}>} Sales)

            =count({<DeptClient = {"=sum(Sales)>=100"} >} distinct DeptClient)

             

            ]

              • Re: Aggr Count Distinct
                Steve Zagzebski

                One quick follow up.  I have an extra layer of complexity where I need to take the combinations that fulfiled the criteria for > 100 sales for 2012 and add an expression that ONLY includes those combinations for 2013 Sales and Distinct Counts:

                 

                DATA:
                DeptClientSalesYEAR
                A1502012Doesn't Meet Criteria
                A1402012Doesn't Meet Criteria
                A2752012Meets Criteria
                A2752012Meets Criteria
                A31502012Meets Criteria
                B11002012Meets Criteria
                B11002012Meets Criteria
                B4502012Doesn't Meet Criteria
                C11502012Meets Criteria
                C51002012Meets Criteria
                C51002012Meets Criteria
                A11002013Doesn't meet criteria because A/1 not valid combination from 2012
                A21252013Meets criteria because A/2 not valid combination from 2012
                A51502013Doesn't meet criteria because A/5 not valid combination from 2012
                B21752013Doesn't meet criteria because B/2 not valid combination from 2012
                C12002013Meets criteria because C/1 not valid combination from 2012
                C12252013Meets criteria because C/1 not valid combination from 2012
                D12502013Doesn't Meet Criteria
                OBJECT:
                DeptTotal Sales of Clients who had at least 100 in salesCount Distinct Clients with Total Sales of 100 or moreTotal Sales from 2013Count Distinct from 2013
                A30021251
                B200100
                C35014251
                • Re: Re: Aggr Count Distinct
                  Steve Zagzebski


                  I attached an excel example in case you have the time to look this over. Thanks again for you help!

                    • Re: Re: Re: Aggr Count Distinct
                      Stefan Wühl

                      There actually is an aggregation grouped by DeptClient:

                       

                      =sum( {< DeptClient = {"=sum(Sales)>=100"} >} distinct DeptClient)

                       

                      You were probably thinking of using advanced aggregation (aggr() ) instead, which should also be possible. I find it easier to read and maintain using set analysis.

                       

                      Adding a set modifier for the YEAR field to the advanced search and the main set expression should solve your second issue.

                       

                      See attached.

                        • Re: Aggr Count Distinct
                          Steve Zagzebski

                          Again  -thanks! I know what I want to do but I struggle with the syntax too often!

                          • Re: Aggr Count Distinct
                            Steve Zagzebski

                            Hopefully this is my last question!

                             

                            This expression works great:

                             

                             

                            sum({<^CustDept = {"=[Commission Dept CummPerc]<=$(vRetensionPct)"}>} Commission)

                             

                            but I need to add the following filter to it:

                            where ProdGLDate is between ProductionBeginDate and ProductionEndDate

                              • Re: Aggr Count Distinct
                                Stefan Wühl

                                Where do the two fields (?) ProductionBeginDate and ProductionEndDate come from? Or are they variables?

                                 

                                In general, you can use an advanced search on your ProdGLDate in your set analysis field modifier, something like (assuming variables):

                                 

                                =sum(

                                {<^CustDept = {"=[Commission Dept CummPerc]<=$(vRetensionPct)"},

                                               ProdGLDate = {">=$(ProductionBeginDate)<=$(ProductionEndDate)"} >}

                                Commission)

                                  • Re: Aggr Count Distinct
                                    Steve Zagzebski

                                    These are not variables - they are part of the load process. I had an example of the expression with variables but can't figure out how to adjust it if these are just regular fields

                                      • Re: Aggr Count Distinct
                                        Stefan Wühl

                                        If these fields only have 1 distinct value each, you can just replace the variable by the field name:

                                         

                                        =sum(

                                        {<^CustDept = {"=[Commission Dept CummPerc]<=$(vRetensionPct)"},

                                                       ProdGLDate = {">=ProductionBeginDate<=ProductionEndDate"} >}

                                        Commission)


                                        If you have several values per field, you need to decide which value to pick, maybe the maximum / minimum?


                                         

                                        =sum(

                                        {<^CustDept = {"=[Commission Dept CummPerc]<=$(vRetensionPct)"},

                                                       ProdGLDate = {">=$(=min(ProductionBeginDate))<=$(=max(ProductionEndDate))"} >}

                                        Commission)

                                          • Re: Aggr Count Distinct
                                            Steve Zagzebski

                                            Thanks - my problem is I need this variable in a prior load so I was not sure how to handle that. I used these two fields (ProductionBeginDate and ProductionEndDate) as variables in that load then just added them to a table and brought them through to the final model.

                                             

                                            Question: what is the best way to get these two variables in a prior QVW and use them in the final model (QVW) too.  I am trying to avoind putting them ni twice (two different files).