Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a complex (maybe only for me) situation. The sample data in a straigth table is:
id | contract_id | entry_date | sign_date | change_date | sign_flag | change_flag |
1 | 1 | 2017-09-11 | 2017-09-11 | 1 | 0 | |
2 | 2 | 2019-01-01 | 2018-08-28 | 2019-01-01 | 0 | 1 |
3 | 2 | 2018-08-28 | 2018-08-28 | 1 | 0 | |
4 | 3 | 2019-05-06 | 2019-05-06 | 1 | 0 | |
5 | 4 | 2015-01-06 | 2015-01-06 | 2015-01-06 | 0 | 1 |
6 | 4 | 2017-01-01 | 2015-01-06 | 2017-01-01 | 0 | 1 |
7 | 4 | 2020-01-01 | 2015-01-06 | 2020-01-01 | 0 | 1 |
8 | 4 | 2015-01-06 | 2015-01-06 | 1 | 0 | |
9 | 5 | 2007-03-22 | 2007-03-22 | 1 | 0 |
It is kind of self explanatory. entry_date has values of change_date if it is not null or sign_date value if change_date is null. contract_id 4 (and it is no mistake) has been signed and changed on the same day, so it has two entries for 2017-12-30.
On the side there is a calendar created for filtering. If the analyst selects, for example, 2017-12-30 - the table needs to show the most recent entry_date for each contract_id that is not greater than the selection.
So, for the selection of 2017-12-30, the table needs to look like this:
id | contract_id | entry_date | sign_date | change_date | sign_flag | change_flag |
1 | 1 | 2017-09-11 | 2017-09-11 | 1 | 0 | |
6 | 4 | 2017-01-01 | 2015-01-06 | 2017-01-01 | 0 | 1 |
9 | 5 | 2007-03-22 | 2007-03-22 | 1 | 0 |
Only one entry per contract needs to be displayed. If the analyst selects 2015-01-06, the table needs to look like this:
id | contract_id | entry_date | sign_date | change_date | sign_flag | change_flag |
5 | 4 | 2015-01-06 | 2015-01-06 | 2015-01-06 | 0 | 1 |
9 | 5 | 2007-03-22 | 2007-03-22 | 1 | 0 |
This needs to be done in expressions, since it is based on selection. Any help would be appreceated.
The [Date] field belongs to the island table [IslandDate]. The selections that the user makes in [Date] do not actually propagate to the [Test] table because there is no field that relates both tables. The maximum value of the [Date] field is used to "filter" the values of the [entry_date] field using set analysis (I attached the qvf file).
If you want to solve the problem using a calculated dimension, you could replace the [id] field with the following...
=If(Aggr(NoDistinct FirstSortedValue({<[entry_date] = {"<=$(=Max([Date]))"}>} Distinct [id], -[entry_date]), [contract_id]) = [id], [id])
Try with this...
Test Script:
SET NullInterpret = '|';
Test:
NOCONCATENATE
LOAD * INLINE [
id, contract_id, entry_date, sign_date, change_date, sign_flag, change_flag
1, 1, 2017-09-11, 2017-09-11, |, 1, 0
2, 2, 2019-01-01, 2018-08-28, 2019-01-01, 0, 1
3, 2, 2018-08-28, 2018-08-28, |, 1, 0
4, 3, 2019-05-06, 2019-05-06, |, 1, 0
5, 4, 2015-01-06, 2015-01-06, 2015-01-06, 0, 1
6, 4, 2017-01-01, 2015-01-06, 2017-01-01, 0, 1
7, 4, 2020-01-01, 2015-01-06, 2020-01-01, 0, 1
8, 4, 2015-01-06, 2015-01-06, |, 1, 0
9, 5, 2007-03-22, 2007-03-22, |, 1, 0
];
IslandDate: // The user must select a date in this field from this island table.
NOCONCATENATE
LOAD Date(MinDate + IterNo(), 'YYYY-MM-DD') AS Date
WHILE MinDate + IterNo() < MaxDate
;
LOAD MIN(entry_date) - 1 AS MinDate, MAX(entry_date) + 1 AS MaxDate
RESIDENT Test;
Measure:
Count({<[id] = {$(=Concat(Aggr(FirstSortedValue({<[entry_date] = {"=$(=Max([Date]))"}>} Distinct [id], -[entry_date]), [contract_id]), ', '))}>} Distinct [id])
Result:
JGMDataAnalysis, thank You for the answer. But is the measure formula ok? Everyting loads fine. Without any selections on the Date field - the overall most recent entries for each contract_id are displayed. But when I try to make selections - nothing happens. It is as if the measure formula does not react to the selections made on the Date field.
Also, would there be a way to create a dimension in place of entry_date field to display the most recent entry dates for each contract_id based on selection on the Date field and leave all other values for that dimension null? Then it would be possible to deselect the "Include null values" option and it would work too without additional count measure. I was trying to achieve this, but the furthest I got was only repeating most recent entry dates for each contract_id entry.
The [Date] field belongs to the island table [IslandDate]. The selections that the user makes in [Date] do not actually propagate to the [Test] table because there is no field that relates both tables. The maximum value of the [Date] field is used to "filter" the values of the [entry_date] field using set analysis (I attached the qvf file).
If you want to solve the problem using a calculated dimension, you could replace the [id] field with the following...
=If(Aggr(NoDistinct FirstSortedValue({<[entry_date] = {"<=$(=Max([Date]))"}>} Distinct [id], -[entry_date]), [contract_id]) = [id], [id])
Aha, now I have located the problem with the # IDs formula in Your first post. It did not have the lesser than (<) symbol in the:
...{"=$(=Max([Date]))"}>}...
Other than that, both solutions now work perfectly fine, but I will be using the second one because it is more aesthetically pleasing for the end user with a little twist in the if statement:
=If(Aggr(NoDistinct FirstSortedValue({<[entry_date] = {"<=$(=Max([Date]))"}>} Distinct [id], -[entry_date]), [contract_id]) = [id], [entry_date])
Now I will be able to use this dimension instead of the original entry_date field.Thank You very much - You are a pro. 🙂