6 Replies Latest reply on Sep 21, 2016 10:42 PM by Sreekanth Kasaraneni

    How to filter  common category id from two dates

    Jenny Sanders

      Hi Guys,

       

      I am trying to filter out on the common category ids between date 1 and date 2 so that it gives me the the category id's not present in date2.

       

      In other words I am trying to achieve something which is similar to a sql query

       

      select category id from table where date = 9/11/2016 and category id not in (select category id from table where date id=9/13/2016).

      Please see the attached workbook which gives an idea of what i am trying to achieve.

       

      The difference in the countd(category ids) gives me the number of categories missing but not the respective id's. Is there any way to see the respective id's?

       

      The output what I am trying to achieve is

       

      category id              

      154

      164

      194

      214

       

      Any leads would be appreciated

       

      Thanks !

        • 1. Re: How to filter  common category id from two dates
          Kajal Chandani

          Hi Jenny,

           

          Hope this solution would help!

          f20.png

           

           

          I am attaching the workbook as well , for your reference.
          Please let me know if it helps.

           

          Thanks and Regards,
          Kajal

          3 of 3 people found this helpful
          • 2. Re: How to filter  common category id from two dates
            Sreekanth Kasaraneni

            Hi Jenny,

             

            i have taken two date parameters which displays Category id's not present in date2

             

            For date parameter 1 created calculated field

             

            IF [Date Parameter1] = [Date] THEN [Category ID] END

             

            For date parameter 2 created calculated field

             

            IF [Date Parameter 2] = [Date] THEN [Category ID] END

             

            and comparing above two calculations

             

            IF ATTR([Category ID for date parameter1]) == ATTR([Category ID for Date Parameter1]) THEN "Matching id's" ELSE "Not-Matching id's" END

             

            img5.jpg

            3 of 3 people found this helpful
            • 3. Re: How to filter  common category id from two dates
              Jenny Sanders

              Thanks Sreekanth ! It worked for my real data.

              • 4. Re: How to filter  common category id from two dates
                Jenny Sanders

                Hi

                 

                @Sreekanth Kasaraneni

                I want the user to be able to filter out on both "matching" and "non-matching" id's. That is in realtime for 9/11/2016 i have 480 records of category id's but for 9/13/2016 i have 472 since 8 of those category id's were closed on 9/11/2016.  The data gets updated daily. So on 9/13/2016 and 9/14/2016 i will have category id's as 472 each i.e. 944 rows by 2  columns.

                 

                Using IF ATTR([Category ID for date parameter1]) == ATTR([Category ID for Date Parameter1]) THEN "Matching id's" ELSE "Not-Matching id's" END '

                 

                I not being able to correctly filter out. My requirement is to show null when there is no difference in the count of the category id's and to show the respective category id's if there is a difference.

                 

                Any leads will be greatly appreciated. I tried by using different calculated fields but couldn't get the filtering to be done correctly.

                 

                I have updated the sample workbook with new data as of 9/14/2016. So for 9/13 and 9/14 since the category-id's are matching i should be able to get "matching" as filter option which i don't see.

                 

                Thanks

                • 5. Re: How to filter  common category id from two dates
                  Jenny Sanders

                  Hey Kajal,

                   

                  It definitely helped me in understanding how to approach. I have a  quick question though.

                   

                  I am trying to come up with a filter which will show null if the category id's from both the dates match i.e. i want to only see the labels and will show category id's that are missing from both the dates. So the filter is dependent on 2 dates that I select.

                   

                  I tried using your LOD calc --

                   

                  if{fixed(category id) : countd(date)!=2} then " Hide" elsif {fixed(category id) :countd(date)==2) then "show" end. This seems to be true if I select a single date only.

                   

                  Any suggestions how to do this?

                   

                  Thanks !

                  • 6. Re: How to filter  common category id from two dates
                    Sreekanth Kasaraneni

                    Hi Jenny,

                     

                    do check the attached workbook sheet2, this might help you.

                     

                    Drop Category ID field to Rows

                    Date field to Marks Card and select Exact date and Discrete options ( Ctrl on Date field and drop to filter shelf)

                     

                    created field status

                    IF WINDOW_SUM(COUNTD([Category ID])) > 1 THEN "Matching id's" ELSE "Un-Matching id's" END

                     

                    Drop status field on to rows and select compute using Date

                     

                    quick filter select two dates (it will not work for single date selection)

                    1 of 1 people found this helpful