Max YearMonth of non-empty values

[hiringel]

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:

YearMonth	Revenue	Expenditure	RevenueIsEmpty
201701	310	312	0
201702	310	208	0
201703	315	350	0
201704	320	310	0
201705	-	250	1
201706	-	380	1
201707	-	310	1

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?

[Anil_Babu_Samineni]

Perhaps this?

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

[sunny_talwar · Accepted solution]

Try this

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