2 Replies Latest reply: Jul 25, 2011 2:26 PM by Stefan Wühl RSS

    Problem with Aggr and maxstring/minstring functions

      I am attaching a small sample showing the concept of what I am trying to accomplish.  Any help would be appreciated.

       

      I am trying to count Sales - there could be a blue sale or a pink sale.   There could also be a Combo sale - both pink and blue.  I am trying to use the maxstring and minstring of the type of sale - if the min and max are blue, then it's a blue sale.  If min and max are pink, then it's a pink sale.  If min is blue and max is pink, then it's a combo sale. 

       

      Because of the way the real data is set up, there could be blank records as well, which I have included in the data. 

       

      I cannot get this to work 100% of the time.  Any thoughts???

       

      Please see the Sales by Color - Not working chart.

       

      Thanks!!!

        • Re: Problem with Aggr and maxstring/minstring functions
          Stefan Wühl

          Hi,

           

          I think I have a solution to your problem, please see attached file.

           

          Instead of the aggr, min/maxstring and if Functions, I used indirect set analysis.

           

          For example

          count({<Customer = p({<Category={'Blue'}, Outcome={'Sale'}>} Customer)*e({<Category={'Pink'}, Outcome={'Sale'}>} Customer) >} DISTINCT Customer)

           

          to count the blue customers.

           

          Hope this helps,

          Stefan

            • Problem with Aggr and maxstring/minstring functions
              Stefan Wühl

              me again,

               

              I was just curious why your solution haven't worked the first place.

               

              I think there might be two problem:

              1) Blank records for Category

               

              which are not NULL, so that Minstring will return those blank records instead of 'blue'.

               

              To fix this you might change the Load expression into:

               

              LOAD SalePerson, Customer, Outcome, if(Category='',Null(),Category) as Category INLINE

              [....]

               

              2)

              the actual expression (here for combo):

              Count(DISTINCT If([Outcome]='Sale' and not isnull(Category) and aggr(minstring([Category]), Customer) = 'Blue' and   aggr(maxstring([Category]), Customer) = 'Pink' ,Customer))

               

              With bob, you get a match here, even you shouldn't. The two conditions for color checking are fulfilled, and you have one record with Outcome = Sale, so it's a combo? No, it is not, because you have to disregard the records which are not 'Sale' also for color checking.

               

              Maybe you could rework this expression in a way that's correct, I assume you finally get something similar to a my set expression.

               

              Hope this helps, too,

              Stefan