Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Try this
=Sum({<YearMonth={"$(=Max({<RevenueEmptyFlag={'0'}>}YearMonth))"}>}Revenue)
Perhaps this?
=Sum({<YearMonth={"=Sum(Revenue) > 0"}>}Revenue)
Try this
=Sum({<YearMonth={"$(=Max({<RevenueEmptyFlag={'0'}>}YearMonth))"}>}Revenue)