topic Re: Selecting default maximum period in QlikView

Selecting default maximum period

Wed, 09 Nov 2016 13:15:13 GMT

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.

Re: Selecting default maximum period

sunny_talwar — Wed, 09 Nov 2016 17:20:19 GMT

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

Re: Selecting default maximum period

Thu, 10 Nov 2016 20:54:31 GMT

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.

Re: Selecting default maximum period

johnw — Thu, 10 Nov 2016 21:27:37 GMT

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'))