topic Re: Expression syntax question in QlikView

Expression syntax question

Wed, 24 Jul 2013 21:38:36 GMT

Hi,

I’m struggling with setting up an expression to be used in a chart.

My dimension is Job_Group

My expression is sum(Duration_In_Minutes)

What I’m trying to achieve is that the expression display data only for the most recent Effective_Date (max Effective_Date?). The chart also needs to be set up so that it doesn’t restrict data based on any other filtering done within the rest of the document.

Any guidance would be greatly appreciated.

Re: Expression syntax question

swuehl — Wed, 24 Jul 2013 21:45:34 GMT

'most recent Effective Date' in global / document context?

Then try maybe something like this as expression:

=sum({1<Effective_Date = {$(=max({1} Effective_Date))}>} Duration_In_Minutes)

Re: Expression syntax question

Wed, 24 Jul 2013 21:57:19 GMT

Yes, Effective_Date is a field generated through the load script.

When I used

=sum({1<Effective_Date = {$(=max({1} Effective_Date))}>} Duration_In_Minutes)

as my expression it returned no data. I'll continue to take a look.

Re: Expression syntax question

Miguel_Angel_Baeyens — Wed, 24 Jul 2013 22:08:27 GMT

Hi,

Is Effective_Date a dimension in your chart? Then I'd try:

Sum(If(Effective_Date = Max(Effective_Date), Duration_In_Minutes))

Use it first with a few rows loaded just to check if it works.

Hope that helps.

Miguel

Re: Expression syntax question

Thu, 25 Jul 2013 15:13:47 GMT

The dimension I'm using is Job_Group.

I think my expression in my chart needs to be something like:

sum

({1<Effective_Date = {max(Effective_Date)}>} Duration_In_Minutes)

But I'm missing something with my syntax - something is wrong as I'm getting an error.

Re: Expression syntax question

Miguel_Angel_Baeyens — Thu, 25 Jul 2013 16:31:36 GMT

Hi,

In regards to syntax:

= {$(=max(Effective_Date))}

This will return a numeric value if Effective_Date has a dual date value (the usual in QlikView). Another option is

= {"$(=Date(max(Effective_Date)), 'YYYYMMDD')"}

should the date is in YYYYMMDD format.

Hope that helps.

Miguel

Re: Expression syntax question

nizamsha — Thu, 25 Jul 2013 16:35:04 GMT

=Count({1<Date={"$(=Max(Date))"}>}Date)

=sum

({1<Effective_Date = {"$(=max(Effective_Date))"}>} Duration_In_Minutes)

Re: Expression syntax question

Thu, 25 Jul 2013 18:37:17 GMT

Thanks Miguel and Nizamsha - very helpful!