Using the Max function in an expression

[james_butler]

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

[JonnyPoole]

Is the last day of the week always say Saturday ? 

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

[james_butler]

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

[stigchel]

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

[rustyfishbones]

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]

)

[JonnyPoole]

Try this as your 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.

[james_butler]

The field Date is in numerical format.