topic Re: Using the Max function in an expression in QlikView

Using the Max function in an expression

james_butler — Mon, 20 Oct 2014 16:05:47 GMT

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

JonnyPoole — Mon, 20 Oct 2014 16:15:24 GMT

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

james_butler — Mon, 20 Oct 2014 16:17:12 GMT

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

Re: Using the Max function in an expression

stigchel — Mon, 20 Oct 2014 16:18:47 GMT

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

rustyfishbones — Mon, 20 Oct 2014 16:20:35 GMT

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]

)

Re: Using the Max function in an expression

JonnyPoole — Mon, 20 Oct 2014 16:20:37 GMT

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.

Re: Using the Max function in an expression

james_butler — Mon, 20 Oct 2014 16:21:22 GMT

The field Date is in numerical format.