Using Identifiers within Set Expressions

[Tig]

Re: Using Identifiers within Set Expressions

Hello,

I am having trouble with the syntax for Set Expressions.  Generally, I want to assign date values to variables, then use the variables in the set Modifier to select a range of dates.  Specifically, where I run into trouble is with the set Identifier.

Scenario 1 -

When the expression below is used in a KPI object, with no identifier, it returns the proper value of 13,017.

• Sum({<StatementDate={">=$(vMaxYearStart) <=$(vMaxStatementDate)"}>} kWh)   // Good: returns 13017

Scenario 2 -

As per the Set Expression help topic; “The Identifier $, represents the records of the current selection.” When the expression below is used, with the $ identifier, it returns the value of 40,675 which is essentially not respecting the range specified in the set Modifier.

• Sum(${<StatementDate={">=$(vMaxYearStart) <=$(vMaxStatementDate)"}>} kWh)   // Bad: returns 40675

Scenario 3 -

As per the Set Expression help topic; “The Identifier 1, represents the full set of all the records in the application, irrespective of any selections made.”  This is the identifier I want but when the expression below is used, with the 1 identifier, it returns a dash.

• Sum(1{<StatementDate={">=$(vMaxYearStart) <=$(vMaxStatementDate)"}>} kWh)   // Bad: returns –

What do I need to do differently to properly use a set identifier in my expression? My sample script is attached as well as the expressions I tested in a KPI object.  Any suggestions would be gratefully received.  Thank you in advance.

[sunny_talwar · Accepted solution]

You are placing your identifier at the incorrect spot... it comes after the opening curly bracket ({) and before the greater than sign (<)

Sum({$<StatementDate={">=$(vMaxYearStart) <=$(vMaxStatementDate)"}>} kWh)

or

Sum({1<StatementDate={">=$(vMaxYearStart) <=$(vMaxStatementDate)"}>} kWh)

[Tig]

Yes!  I feel a mixture of great joy, now that it is working, along with some shame, for having missed that simple error.  Thank you for your help Sunny.  That was awesome.

Tig