Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
microwin88x
Creator III
Creator III

Set Analysis - Bring Last Row (With Value <> Null)

Hello,

I have the following table for a particular Client = 300 (note that dates have format DD/MM/YYYY):

MALLCLIENTUNITCONTRACTCONTRACT_FROMCONTRACT_TOAREACAL_DATE
MALL01300U00650025/01/201723/07/201719501/10/2017
MALL01300U00850308/02/201809/03/2022-01/11/2017


I have Dimensions Mall, Client and Unit, and then all Expressions like Contract, Contract_From, Contract_To and Area.

The following Expression gets the last Contract for the last Date (Cal_Date) for each Mall/Client/Unit:

=MAX(IF(AGGR(NODISTINCT MAX(CAL_DATE),MALL)=CAL_DATE,CONTRACT))

It works fine, but the thing is that this case, for the last Cal_Date (01/11/2017) has Area = NULL and I'd need to modify the Expression in order to get those Contracts whose Area is not null for the last Cal_Date and Contract_From is not null too. In this case, I should get the first row of the table with Contract = 500.

Do you know how could I do that?

Thank you!!!

3 Replies
sunny_talwar

Not sure I follow the description... would you be able to share a sample to look at?

Anil_Babu_Samineni

Perhaps this? But, Really not sure your problem

=MAX(IF(AGGR(NODISTINCT MAX(CAL_DATE),MALL)=CAL_DATE and Not IsNull(AGGR(NODISTINCT MAX(CAL_DATE),MALL))=CAL_DATE,CONTRACT))


Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
microwin88x
Creator III
Creator III
Author

Hello Sunny stalwar1‌,

I send you the table with data:

MALLCLIENTUNITCONTRACTCONTRACT_FROMCONTRACT_TOAREACAL_DATE
MALL01300U00650025/01/201723/07/201719501/01/2017
MALL01300U00650025/01/201723/07/201719501/01/2017
MALL01300U00650025/01/201723/07/201719501/02/2017
MALL01300U00650025/01/201723/07/201719501/02/2017
MALL01300U00650025/01/201723/07/201719501/03/2017
MALL01300U00650025/01/201723/07/201719501/03/2017
MALL01300U00650025/01/201723/07/201719501/04/2017
MALL01300U00650025/01/201723/07/201719501/05/2017
MALL01300U00650025/01/201723/07/201719501/06/2017
MALL01300U00650025/01/201723/07/201719501/07/2017
MALL01300U00650025/01/201723/07/201719501/08/2017
MALL01300U00650025/01/201723/07/201719501/09/2017
MALL01300U00650025/01/201723/07/201719501/10/2017
MALL01300U00850308/02/201809/03/2022-01/11/2017
MALL02300U01080018/12/201417/12/201825001/01/2017
MALL02300U01080018/12/201417/12/201825001/02/2017
MALL02300U01080018/12/201417/12/201825001/03/2017
MALL02300U01080018/12/201417/12/201825001/04/2017
MALL02300U01080018/12/201417/12/201825001/05/2017
MALL02300U01080018/12/201417/12/201825001/06/2017
MALL02300U01080018/12/201417/12/201825001/07/2017
MALL02300U01080018/12/201417/12/201825001/08/2017
MALL02300U01080018/12/201417/12/201825001/09/2017
MALL02300U01080018/12/201417/12/201825001/10/2017
MALL02300U01080018/12/201417/12/201825001/11/2017
MALL02300U01080018/12/201417/12/201825001/12/2017
MALL03525U24090023/10/2015-21001/01/2017
MALL03525U24090023/10/201501/01/201721001/02/2017
MALL03525U24090023/10/201501/02/201721001/03/2017
MALL03525U24090023/10/201501/03/201721001/04/2017
MALL03525U24090023/10/201501/04/201721001/05/2017
MALL03525U24090023/10/201501/05/201721001/06/2017
MALL03525U24090023/10/201501/06/201721001/07/2017
MALL03525U24090023/10/201501/07/201721001/08/2017
MALL03525U24090023/10/201501/08/201721001/09/2017
MALL03525U24090023/10/201501/09/201721001/10/2017
MALL03525U24090023/10/201501/10/201721001/11/2017
MALL03525U24090023/10/201501/11/201721001/12/2017
MALL03525U240950---01/12/2017

I need to show a table with Dimensions Mall, Client and Unit with Expressions Contract, Contract_From, Contract_To and Area with the following data (rows in bold😞

MALLCLIENTUNITCONTRACTCONTRACT_FROMCONTRACT_TOAREACAL_DATENotes
MALL01300U006500 25/01/201723/07/201719501/10/2017Show this row
MALL01300U00850308/02/201809/03/2022-01/11/2017No because Area is Null
MALL02300U01080018/12/201417/12/201825001/12/2017Show this row
MALL03525U24090023/10/201501/11/201721001/12/2017Show this row
MALL03525U240950---01/12/2017No because Contract and Area is Null

The current Expressions I have are the following, for example to get the last Contract:

=MAX(IF(AGGR(NODISTINCT MAX(CAL_DATE),MALL)=CAL_DATE,CONTRACT))

** Note that I always select first 1 CLIENT from a Listbox in order to show that table **


Thanks.