Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
hiringel
Contributor III
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
sunny_talwar

Try this

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

View solution in original post

2 Replies
Anil_Babu_Samineni

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
sunny_talwar

Try this

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