Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
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

Highlighted
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