0 Replies Latest reply: Apr 22, 2011 11:10 AM by MTEUser _ RSS

    Help with Min(,) Function

    MTEUser _

      I have a report showing the item number and the quantity sold for each of the last 12 months.  I'd like to know the fewest quantity sold in any month so I used the Min(,) function.  This works great but if there was a month where none was sold, it returns zero.  I'd like it to return the minimum greater than zero.

        • Help with Min(,) Function
          Olly Bond

          Hello MTEUser,

           

          If you define a filter of Sales>0 and set your summary to use this filter, you should get what you need.

           

          Best wishes

           

          Olly

            • Help with Min(,) Function
              Steve Caiels

              [FONT=Calibri]Hi MTEUser,[/FONT][/SIZE]

               

              [FONT=Calibri]Are using the Min(,) function in the table rather than the Min Aggregation in the summary window?  /FONT[/SIZE]

               

              [FONT=Calibri]If so, I guess you have a table with all 12 months on eachrow? So your expression is Min(Jan,Feb,Mar . . . Dec).[/FONT][/SIZE]

               

              [SIZE=3]One option would be to export the table as fixed width text,then bring it back in to Monarch using the MCR feature.  From there, you could filter zero sales inthe table and summarise it.[/SIZE][/FONT]

               

              [SIZE=3]Or, if you prefer to do this in one pass through Monarch, youcan create a calculated field for each month with a massive ‘false’ sales valuein it when sales are really 0.  That way,the zero sales will never be the smallest value.  You may have to allow for the situation wheresales for every month are zero.[/SIZE][/FONT]

               

              [FONT=Calibri]For example, if sales of 999999 are beyond any realms of possibility,create calculated fields called FalseJan, FalseFeb,FalseMar etc. withexpressions of[/FONT][/SIZE]

               

              [FONT=Calibri]If(Jan = 0,999999,Jan) /FONT[/SIZE]

               

              [FONT=Calibri]Then create a field called FalseMin using the expressionMin(FalseJan,FalseFeb,FalseMar . . FalseDec)[/FONT][/SIZE]

               

              [FONT=Calibri]And to capture months where all sales a zero, a final calculatedfield of If(FalseMin = 999999,0,FalseMin)[/FONT][/SIZE]

               

              [FONT=Calibri]You could combine this all into one calculated field if youwish. /FONT[/SIZE]

               

               

              [FONT=Courier New]If(/FONT[/FONT]

              [FONT=Courier New] Min(/FONT /FONT

              [FONT=Courier New]     If(Jan= 0,999999,Jan),[/FONT]

                   If(Feb= 0,999999,Feb),[/FONT]

                   Etc..[/FONT]

                   If(Dec= 0,999999,Dec)[/FONT]

                   )

                =999999,[/FONT]

              0,[/FONT]

              M[/FONT][FONT=Courier New]in(/FONT

                   If(Jan= 0,999999,Jan),[/FONT]

                   If(Feb= 0,999999,Feb),[/FONT]

                   Etc..[/FONT]

                   I[FONT=Courier New]f(Dec= 0,999999,Dec)[/FONT]

                   )[/FONT]

              )[/FONT]

               

               

              /FONT[/QUOTE]

               

               

               

              Regards,[/FONT]

               

              Steve.[/FONT]