Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
erric3210
Creator
Creator

check if Selected Year value exists in last 2 field value - Include/Exclude Rows

Hi All,

I've a below requirement.
Sharing sample data.

LOAD * INLINE [
Area, Product, Financial year, Order
New York, AA, 2020, 1
New York, AA, 2021, 2
New York, BB, 2022, 3
New York, CC, 2023, 4
Chicago, DD, 2021, 1
Chicago, EE, 2022, 2
Chicago, FF, 2023, 3
];

Requirement:

//**** When someone selects Area Field = 'New York'

When someone selects on 'New York' Area and selects [Financial year] '2020' then it should hide that row
Reason: It's the first value of the selected Area ('New York')
(Need to compare current selected value with last 3 year values for the selected Area i.e. 'New York')

When someone selects on 'New York' Area and selects [Financial year] '2021' then it should hide that row
Reason: There is no change in the current selected value and previous year Product value
(Need to compare current selected value with last 3 year values for the selected Area i.e. 'New York')

When someone selects on 'New York' Area and selects [Financial year] ' 2022' then it should only display that row
Reason: There is a change in the current selected value and previous year Product values
(Need to compare current selected value with last 3 year values for the selected Area i.e. 'New York')

When someone selects on 'New York' Area and selects [Financial year] '2023' then it should only display that row
Reason: There is a change in the current selected value and previous year Product values
(Need to compare current selected value with last 3 year values for the selected Area i.e. 'New York')


//**** When someone selects Area Field = 'Chicago'

When someone selects on 'Chicago' Area and selects [Financial year] '2021' then it should hide that row
Reason: It's the first value of the selected Area field ('Chicago')
(Need to compare current selected value with last 3 year values for the selected Area i.e. 'Chicago')


When someone selects on 'Chicago' Area and selects [Financial year] '2022' then it should only display that row
Reason: There is a change in the current selected value and previous year Product values
(Need to compare current selected value with last 3 year values for the selected Area i.e. 'Chicago')

When someone selects on 'Chicago' Area and selects [Financial year] '2023' then it should only display that row
Reason: There is a change in the current selected value and previous year Product values
(Need to compare current selected value with last 3 year values for the selected Area i.e. 'Chicago')


I've tried IF expression in Text Box but it is lengthy. I need to try it with Set Analysis using P() And E()

Here is the Expression, I've tried:

=IF(
(LEN(CONCAT({<[Financial year]={'$(=Max([Financial year])-1)','$(=Max([Financial year])-2)','$(=Max([Financial year])-3)'}>} DISTINCT CHR(39)&Product&CHR(39),', '))=0)
OR
(CONCAT({<[Financial year]={'$(=Max([Financial year]))'}>} DISTINCT CHR(39)&Product&CHR(39),', ')=
CONCAT({<[Financial year]={'$(=Max([Financial year])-1)','$(=Max([Financial year])-2)','$(=Max([Financial year])-3)'}>} DISTINCT CHR(39)&Product&CHR(39),', ')),'Old',

IF(
(MATCH(
CONCAT({<[Financial year]={'$(=Max([Financial year]))'}>} DISTINCT CHR(39)&Product&CHR(39),', '),
CONCAT({<[Financial year]={'$(=Max([Financial year])-1)','$(=Max([Financial year])-2)','$(=Max([Financial year])-3)'}>} DISTINCT CHR(39)&Product&CHR(39),', ')))
OR
(NOT MATCH(
CONCAT({<[Financial year]={'$(=Max([Financial year]))'}>} DISTINCT CHR(39)&Product&CHR(39),', '),
CONCAT({<[Financial year]={'$(=Max([Financial year])-1)','$(=Max([Financial year])-2)','$(=Max([Financial year])-3)'}>} DISTINCT CHR(39)&Product&CHR(39),', '))),'New'))

It's giving right result in Text Box but wrong Result when using it in Chart


Need your help in tweaking the expression to Set Analysis Expression

Regards,
Eric

Labels (5)
0 Replies