Set analysis to filter table based on user selection

[Anonymous]

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.

[sunny_talwar]

Where exactly are you trying to see this? Straight table? What is your dimension here?

[Anonymous]

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.

[sunny_talwar · Accepted solution]

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])

[Anonymous]

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.

[sunny_talwar]

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])

[Anonymous]

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.

[sunny_talwar]

Awesome