Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
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'))