SET ANALYSIS

[jduenyas]

Hi all

In an example of set analysis which I have downloaded, an expression was written as such:

=if(sum({$<YR = {$(=max(YR - 1))}, MO = {"<=$(=max({<YR={$(=max(YR))}>} MO))"}>}  TranAmt)
> sum({$<YR = {$(=max(YR))}, MO = {"<=$(=max({<YR={$(=max(YR))}>} MO))"}>}  TranAmt),
sum({$<YR = {$(=max(YR))}, MO = {"<=$(=max({<YR={$(=max(YR))}>} MO))"}>}  TranAmt),
sum({$<YR = {$(=max(YR) - 1)}, MO = {"<=$(=max({<YR={$(=max(YR))}>} MO))"}>}  TranAmt)
)

Although it is a bit convoluted I can descern that  Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}>}  is to find the Month which is "less than or equal Max Month of the Max Year".

My question though is regarding the double quote: When / where / why use double quotes vs single quotes if use any of them at all? And is there any written material regarding advanced construct and punctuation of expressions within set analysis?

Thanks

[Gysbert_Wassenaar · Accepted solution]

There used to be a difference between single and double quotes in set analysis expressions. As far as I know there isn't anymore. That's a good thing because it makes it possible to nest search strings.

The expression you posted can be simplified some using the rangemin function:

rangemin(

sum({$<YR = {$(=max(YR)-1)}, MO = {"<=$(=max({<YR={$(=max(YR))}>} MO))"}>}  TranAmt) ,

sum({$<YR = {$(=max(YR))}, MO = {"<=$(=max({<YR={$(=max(YR))}>} MO))"}>}  TranAmt)

)

or with some helper variables:

vCurrentYear: =max(YR)

vPrevYear: =max(YR)-1

vSumTranAmt: sum({$<YR = {$1}, MO = {"<=$(=max({<YR={$(=max(YR))}>} MO))"}>}  TranAmt)

Expression: rangemin( $(vSumTranAmt($(vPrevYear))), $(vSumTranAmt($(vCurrentYear))) )

[jduenyas]

Thank you Gysbert. You always come through shining!

One correction though:

Shouldn't vPrevYear = max(YR-1)  actually be vPrevYear = max(YR) -1 ?

Thanks again.

[Gysbert_Wassenaar]

Yeah, you're right. So much for blindly copying and pasting stuff people post at random sites . I've fixed the code now. Thanks.