Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Using set analysis I was able to filter all rows that were less than the date a user selected from the filter pane by creating a column with the following formula:
max({$<[Date Approved] = {"<=$(=DATE(max(GetFieldSelections([Date Approved]))))"}>} [Date Approved])
I have been stuck trying to find out how to filter the resultant table so that ONLY the max([Date Approved]) of each [Data Partner].
I have also generated a calendar and put it into a filter pane so when a user selects one date in it it will be picked up by the table listening with GetFieldSelections([Date Approved])
For example I have the original dataset
Data Partner Date Approved ETL Num
DP1 1/1/2015 9
DP2 4/6/2014 7
DP3 3/14/2015 13
DP3 6/4/2015 14
DP1 5/6/2015 11
I included the column ETL Num because it may be easier to filter again by this number as this number will get larger as the Date Approved increases.
When a user then click on another item on the sheet which contains the calendar [Date Approved] the table then filters as I expected. (the column in this resultant table contains the above formula)
Ex user selects date 7/7/2015
Resultant table:
Data Partner Date Approved ETL Num
DP1 1/1/2015 9
DP2 4/6/2014 7
DP3 3/14/2015 13
DP3 6/4/2015 14
DP1 5/6/2015 11
Instead of this I want the table to look like:
Data Partner Date Approved ETL Num
DP2 4/6/2014 7
DP3 6/4/2015 14
DP1 5/6/2015 11
Now say the user selects the date 5/5/2015
Resultant Table:
Data Partner Date Approved ETL Num
DP1 1/1/2015 9
DP2 4/6/2014 7
DP3 3/14/2015 13
In this case I want the resultant table to look like:
Data Partner Date Approved ETL Num
DP1 1/1/2015 9
DP2 4/6/2014 7
DP3 3/14/2015 13
Any Advice on how to go about filtering the table so it only displays one row per Data Partner is much appreciated.
Try this:
Dimension
Data Partner
Expression
1) Max({$<[Date Approved] = {"<=$(=DATE(max(GetFieldSelections([Date Approved]))))"}>} [Date Approved])
2) FirstSortedValue({$<[Date Approved] = {"<=$(=DATE(max(GetFieldSelections([Date Approved]))))"}>} [ETL Num], -[Date Approved])
Where exactly are you trying to see this? Straight table? What is your dimension here?
Hi.
Yup I am trying to view this all in a table. there are dimensions ETL Num and Data Partner. There is a measure Date Approved which contains the formula: max({$<[Date Approved] = {"<=$(=DATE(max(GetFieldSelections([Date Approved]))))"}>} [Date Approved])
There is also a Date Approved Column in my original data set which contains the actual date the ETL was approved. I generated a calendar based on the max and min date from this column in the table and named it the same Date Approved. I the placed this dimension in a filter pane so I could allow the user to select a date and then filter the dataset in the table based on this user's choice in date.
So in terms of workflow the table originally appears blank until the user selects a date from the filter pane and then the table populates with resultant table: I noted above.
Try this:
Dimension
Data Partner
Expression
1) Max({$<[Date Approved] = {"<=$(=DATE(max(GetFieldSelections([Date Approved]))))"}>} [Date Approved])
2) FirstSortedValue({$<[Date Approved] = {"<=$(=DATE(max(GetFieldSelections([Date Approved]))))"}>} [ETL Num], -[Date Approved])
Hi,
Unfortunately this didn't work.
so I have the following table before selecting a date:
Data Partner Date Approved ETL Num
I select a date like: 7/7/2015 from my filter pane calendar.
and get:
Data Partner Date Approved ETL Num
DP1 1/1/2015 9
DP2 4/6/2014 7
DP3 3/14/2015 13
DP3 6/4/2015 14
DP1 5/6/2015 11
where data partner is a dimension, and Date Approved and ETL Num are respectively:
Expression
1) Max({$<[Date Approved] = {"<=$(=DATE(max(GetFieldSelections([Date Approved]))))"}>} [Date Approved])
2) FirstSortedValue({$<[Date Approved] = {"<=$(=DATE(max(GetFieldSelections([Date Approved]))))"}>} [ETL Num], -[Date Approved])
Is there a way to then remove the rows:
DP1 1/1/2015 9
DP3 3/14/2015 13
from the resultant table.
Check the attached
Expression used:
Max({$<[Date Approved] = {"<=$(=Date(Max([Date Approved])))"}>} [Date Approved])
FirstSortedValue({$<[Date Approved] = {"<=$(=Date(Max([Date Approved])))"}>}[ETL Num], -[Date Approved])
Thank you for the example. This works.
I noticed the problem in my program was that I has a lot of extra columns such as Cause of Death Present (only containing a boolean 1 or 0) which seemed to allow for repeats where say DP1 has in either of these two cases has a 1 and another case has a 0.
This does not need to be shown on this tab though.
Awesome