Set Analysis using Left function

[zagzebski]

My data sctucture:

Product	Invoiced Year	Invoiced YearMonth	Paid YearMonth	Amount
A	2012	201201	201202	25
A	2012	201202	201205	50
A	2012	201303	201206	75
A	2012	201204	201204	100
A	2013	201301	201306	125
A	2013	201302	201304	150
A	2013	201303	201303	175
A	2013	201304	201305	200

Current Object Expressions:

Current Year Amount:

=Sum ({$<[Incurred Year] = {$(=Max([Incurred Year]))}>}[Amount])

Prior  Year Amount:

'=Sum ({$<[Incurred Year] = {$(=Max([Incurred Year]-1))}>}[Amount])

These expression work fine but I want to add the additonal filter for Paid Year just like I have it for Incurred Year in the expressions. However there is no Paid Year in the data so I am wondering how you can get the left 4 characters from the Paid YearMonth and then use it like is being used for the Incurred Year in the expressions above.

[jpapador]

Why not just make a paid year field in the script.  Left([Paid YearMonth], 4) as PaidYear

[zagzebski]

Unfortunately I am not always in control of the script - as in this case. Also I seem to come across this need at various times when I want to use a just portion of a field in my set analysis in combination with max/min.

[whiteline · Accepted solution]

Hi.

You have to choose some field to make selection, assume [Paid YearMonth].

Then you could use search capabilities of set analysis (selection by expression):

=Sum ({$<[Paid YearMonth] = {"=Left( [Paid YearMonth] ,4)=$(=Left(Max( [Paid YearMonth])), 4)"}>}[Amount])

[zagzebski]

Thanks!

So I understand - why aren't we able to just eliminate the middle part and have something like this?:

<[Paid YearMonth] =$(=Left(Max( [Paid YearMonth])), 4)"}>}

Instead of:

<[Paid YearMonth] = {"=Left( [Paid YearMonth] ,4)=$(=Left(Max( [Paid YearMonth])), 4)"}>}

I know there is a good reason. Thanks so much in advance!

[whiteline]

Because Paid YearMonth contains values like YYYYMM, while left(...,4) returns only YYYY.

The suggested set expression filters those  Paid YearMonth values for which the expression between quotes is true.

[zagzebski]

Hi -

I thought this expression was working but when I set it alone in an expression it returns 0 for every line (no mater what I select on the Paid YearMonth list box):

Sum ({$<[Paid YearMonth] = {"=Left( [Paid YearMonth] ,4)=$(=Left(Max( [Paid YearMonth])),4)"}>}[DHP Liability Amount])