2 Replies Latest reply on Oct 27, 2018 11:46 AM by Tina Crouse

    Create a quick filter but exclude certain items

    Tina Crouse

      I am trying to create a quick filter so the end-user can filter on the dashboard but there are some values in the dimension that I need to exclude from the filter. Example of the data is I have the states and need to put those states into regions and the include a sub region but there are certain sub regions by the line of business. So what I have is this:


      A view that allows me to filter by multiple lines of business. Commercial, Medicare, and Medicaid. The view shows like this:


      checkbox                        state            sum of visit for jan17             sum of visit for feb17    etc to dec17 and it is a graph view

      Medicare                          CA                              7                                             7                                             7

      Medicaid                          CO                              7                                             7                                             7

      Commercial                     CT                               7                                             7                                             7


      I can create a calculation and roll up the regions. Not a problem. The issue is with the sub regions because each line does not use the same roll up for that. For Medicare there is no sub region. For Medicaid, CA and CO would be in sub region 1 but CT for Medicaid is in sub region 5 and for Commercial it is sub region 6.


      When I write the if calculation for all of this, how do I say you know, do not look at Medicare data just leave the Medicare intact on the dashboard. Like some sort of

      if lob = 'Medicaid' and state  'ca' or state = 'co' then 'sub region 1' if lob = 'Medicaid' and state = 'CT' then 'Sub Region 5' if lob = 'Commercial'

      and state = 'CT' then 'Sub Region 6' if {exclude lob = 'Medicare'} end


      I do not want Medicare to disappear when any sub region is selected since they do not have sub regions. Only 3 regions. So if Medicare and Medicaid are both checked and then region West, the right states would appear. Only relevant data is then left in the sub region filter and if I select sub region 1 the right Medicaid states appear but by default all the Medicare states that fall under West would still remain. Does that make sense? I cannot post a workbook due to privacy issues because I work for a health insurer and I cannot do an example with partial data because I have tried it in the past and trying to do a small amount of data with our 13 million plus rows makes it so things fall out. Like I tried to do 10k rows and posted that WB anonymous and because there was not enuf data I could not get an answer. If no one can help me then I will just have to continue Googling until I figure it out which I normally do just don't like taking weeks to figure it out. Especially when I have to turn this around by next week.

        • 1. Re: Create a quick filter but exclude certain items
          Deepak Rai


          Help us not to get lost in big description. Can you say in few lines please.



          • 2. Re: Create a quick filter but exclude certain items
            Tina Crouse

            I have all three lines of business in one view. Medicare, Medicaid, and Commercial. I need to add regions West, Central, and East. I can do that with an if statement and the list of states by line of business. My issue is the sub regions do not apply for Medicare and the sub regions vary slightly for Medicaid and Commercial. If I filter let's say West. All 3 lines will still show up and the 2nd quick filter will only show the sub regions that apply to the west. There will be 2, sub region 1 for Medicaid and sub region 2 for Commercial. If I select sub region 1, Commercial rightly so should disappear, but because Medicare has no sub regions, the states that fall into the West need to remain. I want to keep whatever information is filter by the region for Medicare when a sub region is selected since Medicare has no sub regions and I do not want a null or exclude or Medicare listed in my sub region filter. It can only be sub region 1 through 6.