7 Replies Latest reply: Dec 8, 2016 2:20 PM by Sunny Talwar RSS

    Set analysis to filter table based on user selection

    Adam Paczuski

      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

          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
              Adam Paczuski

              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

                  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
                      Adam Paczuski

                      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.