topic Set analysis to filter table based on user selection in App Development

Set analysis to filter table based on user selection

Anonymous — Thu, 08 Dec 2016 15:14:52 GMT

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.

Re: Set analysis to filter table based on user selection

sunny_talwar — Thu, 08 Dec 2016 15:31:15 GMT

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

Re: Set analysis to filter table based on user selection

Anonymous — Thu, 08 Dec 2016 15:46:47 GMT

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.

Re: Set analysis to filter table based on user selection

sunny_talwar — Thu, 08 Dec 2016 16:46:57 GMT

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

Re: Set analysis to filter table based on user selection

Anonymous — Thu, 08 Dec 2016 17:41:33 GMT

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.

Re: Set analysis to filter table based on user selection

sunny_talwar — Thu, 08 Dec 2016 17:55:24 GMT

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

Re: Set analysis to filter table based on user selection

Anonymous — Thu, 08 Dec 2016 18:19:47 GMT

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.

Re: Set analysis to filter table based on user selection

sunny_talwar — Thu, 08 Dec 2016 19:20:19 GMT

Awesome