Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Max YearMonth of non-empty values

Hi,

I have a table where some fields have values for july 2017, while others for only april 2017. I want to have a textbox show me the latest value of each field. I am trying to do it in a set analysis with little success:

YearMonthRevenueExpenditureRevenueIsEmpty
2017013103120
2017023102080
2017033153500
2017043203100
201705-2501
201706-3801
201707-3101

In my transformation layer, I created an EmpyFlag for each field, in hope of using that for my selection. So my wish is to show the latest value (max YearMonth) where the RevenueEmptyFlag=0.

=sum({<YearMonth={">=\$(max({<RevenueEmptyFlag={'0'}>}YearMonth))"}>}Revenue)

This however returns a sum of all Revenue values, which is weird. Because

=sum({<YearMonth={"201704"}>}Revenue) gives me the correct value.

How do I make the YearMonth be the latest of the non-empty Revenue values?

1 Solution

Accepted Solutions
MVP

Try this

=Sum({<YearMonth={"\$(=Max({<RevenueEmptyFlag={'0'}>}YearMonth))"}>}Revenue)

2 Replies

Perhaps this?

=Sum({<YearMonth={"=Sum(Revenue) > 0"}>}Revenue)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
MVP

Try this

=Sum({<YearMonth={"\$(=Max({<RevenueEmptyFlag={'0'}>}YearMonth))"}>}Revenue)

Community Browser