Show the latest Record in Filter Pane

[has35526]

Hi Everyone,

I need to show the latest record in the filter pane. The firstsortedvalue is working as expected but only when I created a measure. Is there a way to show the latest record only in the filter pane.

For example: I only want to show NLS-FSMFTB in the filter pane. 

NLS

NLS- FMFTB

[Anil_Babu_Samineni]

@has35526 That is the goal, right? IF just one you want to show always, can be static like this.

If(Match(company_name, 'NLS- FMFTB'), company_name)

[has35526]

@Anil_Babu_Samineni 

Below query in the database is giving us the correct result

SELECT fcci.crid, fcci.company_name, fap.master_mid, fap.acc_num AS master_epa, fap.acc_num, fap.prod_wrkflw_name, fap.prod_grp_name, DATE_TRUNC('month', DATEADD(MONTH, '0', CURRENT_DATE)) AS processing_date
FROM fcci
INNER JOIN (select crid, mid, acc_num, wrkflw_name, grp_name, DATE_TRUNC('month', start_date) as start_date, LAST_DAY (end_date) as end_date
from (
select *, row_number() OVER (PARTITION BY crid, wrkflw_name, LAST_DAY (start_date) ORDER BY start_date DESC) AS row_number
from (select * from facts_profile where master_mid = mid)
)
WHERE row_number = 1) fap ON fcci.crid = fap.crid
AND DATE_TRUNC('month', DATEADD(MONTH, '0', CURRENT_DATE)) BETWEEN fap.start_date AND NVL(fap.end_date, SYSDATE);

[Gabbar]

Why Dont you create a Flag or another Field in Script only and than use that as a filter like:-


Load Company_name as C.Name,crid&master_mid&-------- as key1;
SQL
SELECT fcci.crid, fcci.company_name, fap.master_mid, fap.acc_num AS master_epa, fap.acc_num, fap.prod_wrkflw_name, fap.prod_grp_name, DATE_TRUNC('month', DATEADD(MONTH, '0', CURRENT_DATE)) AS processing_date
FROM fcci
INNER JOIN (select crid, mid, acc_num, wrkflw_name, grp_name, DATE_TRUNC('month', start_date) as start_date, LAST_DAY (end_date) as end_date
from (
select *, row_number() OVER (PARTITION BY crid, wrkflw_name, LAST_DAY (start_date) ORDER BY start_date DESC) AS row_number
from (select * from facts_profile where master_mid = mid)
)
WHERE row_number = 1) fap ON fcci.crid = fap.crid
AND DATE_TRUNC('month', DATEADD(MONTH, '0', CURRENT_DATE)) BETWEEN fap.start_date AND NVL(fap.end_date, SYSDATE);


now this key1 will link to you table and use C.Name in Filter pane or
use  create a calulated Dimension Using this C.name and aggreate it on Company_Name;

Because this is a quite a good query and creating set analysis of this will require  a dataset of all those columns with atleast 1000 rows

and still then there will be problems while using Possible Function,
as it will require atleast 2 possible functions according me and then which filters should affect or not affect possible functions are another case.

[Anil_Babu_Samineni]

If this is already capturing directly writing SQL. Then you can load with this query into Qlik and see how the result looks like? 

If due to join limitation, I would rather think to store all the SQL query into Views and call only view into Qlik.