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
