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

Selecting default maximum period

The following code which works perfectly fine combined with a PIT master calendar: 

=Sum({<TargetPeriod={"$(=(SalePeriod))"}, Salesman=, SaleFiscalYear=, SaleFiscalYear=, [TargetGroup] =, SaleWeekDay=,SaleQuarter=, SaleMonth= >} Target)

i.e. if a date is selected then it sums up the Target for  the target period (multiple salespeople  have targets within the same TargetPeriod).

However what I  would like to do is default to the latest target/sales period, if no date selections have been made with an if statement. I was hoping it would be a simple case of:

IF (getselectedcount(SaleYear) 1,

Sum({<TargetPeriod={"$(=(SalePeriod))"}, Salesman=, SaleFiscalYear=, SaleFiscalYear=, [TargetGroup] =, SaleWeekDay=,SaleQuarter=, SaleMonth= >} Target),

  Sum({<TargetPeriod={"$(=Max(SalePeriod))"}, Salesman=, SaleFiscalYear=, SaleFiscalYear=, [TargetGroup] =, SaleWeekDay=,SaleQuarter=, SaleMonth= >} Target))

But no joy I get 0 when using the second statement in isolation with the max function. I notice SalePeriod on it’s own fine. But  Max(SalePeriod)  returns nothing.

Any help much appreciated thanks.

3 Replies

Re: Selecting default maximum period

Is SalePeriod a text field? May be you need to make sure that it is numeric field or use MaxString(SalePeriod) to see if that works

Not applicable

Re: Selecting default maximum period

Hi Sunny,

Many thanks for getting back to me. Indeed it is a string  e.g. Nov-2016 I tried maxstring  but no joy on that one.

MVP
MVP

Re: Selecting default maximum period

You really need the maximum date, not the maximum string value, since Dec-2016 would come before Jan-2016, and the months would all sort before the years sorted, and so on. If you don't have a date version of the sale period, then maybe convert it to a date, get the max, then convert it back to a string representation. Something like this:

$(=date(max(date#(SalePeriod,'MMM-YYYY')),'MMM-YYYY'))

Community Browser