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

# Help with Min(,) Function

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

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

[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]

• ###### Help with Min(,) Function

I wouldn't have thought of this trick, this is exactly what I need, thanks so much for your assistance!

• ###### Help with Min(,) Function

Hi MTE User, hi Steve,

One possible refinement might be to define the 999999 figure as Max(Jan;Feb;...;Dec)+1, so that it's always going to exceed the highest figure.

Best wishes,

Olly

• ###### Help with Min(,) Function

Hello

I recommend reading over this site, [URL is no longer valid], it shoudl have alot of tips for you.