Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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])
Why not just make a paid year field in the script. Left([Paid YearMonth], 4) as PaidYear
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.
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])
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!
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.
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])