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.



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



          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,


            • 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




              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,