Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

james_butler
New Contributor III

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

Tags (3)
6 Replies
Highlighted
Employee
Employee

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...

james_butler
New Contributor III

Re: Using the Max function in an expression

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

stigchel
Honored Contributor

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

rustyfishbones
Honored Contributor II

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]

)

Employee
Employee

Re: Using the Max function in an expression

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
New Contributor III

Re: Using the Max function in an expression

The field Date is in numerical format.

Community Browser