6 Replies Latest reply: Oct 20, 2014 12:21 PM by James Butler

# Using the Max function in an expression

Hi

I'm wanting to sum the value in stock for the last day in the week (to get a closing balance). I thought max would help but i haven't had any luck with the below expression.... Any ideas why?

sum(

{\$<

Year=,

Week=,

Date = {\$(=max(Date))},

IsTrackedPart = {1}

>}

[Inventory Quantity in Stock]

)

Thanks

James

• ###### Re: Using the Max function in an expression

Is the last day of the week always say Saturday ?

=Date(Date,'WWWW')    will return full days like  'Saturday', 'Friday' etc...

• ###### Re: Using the Max function in an expression

Correct, Saturday is always the last day in the week.

• ###### Re: Using the Max function in an expression

if(  Date(Date,'WWWW') = 'Saturday' and IsTrackedPart = 1, sum(   [Inventory Quantity in Stock] ) )

or this:

sum ( if(  Date(Date,'WWWW') = 'Saturday' and IsTrackedPart = 1,  [Inventory Quantity in Stock] ) )

and ensure suppress zero values is enabled on the presentation tab of the chart properties.

• ###### Re: Using the Max function in an expression

Your Date field is probably a real date format field, in that case the right hand side of your compariosn in the set analysis is expecting a text format, so you will need quotation marks around the \$(=) and you will need to format the text to match the format of your date field. Use the date function for that.

Date = {'\$(=Date(max(Date),'DD-MM-YYYY')'}

Replace the 'DD-MM-YYYY' with your own format

• ###### Re: Using the Max function in an expression

The field Date is in numerical format.

• ###### Re: Using the Max function in an expression

Hi James,

Try adding Weekday in your Calendar and then change the Expression to.

sum(

{\$<

Year=,

Week=,

WeekDay = {'Sat'},

IsTrackedPart = {1}

>}

[Inventory Quantity in Stock]

)