10 Replies Latest reply: Mar 21, 2013 1:13 PM by Srini VEERAVALLI RSS

    Column filter help

    ZFMA86
      Gurus,

      Using OBIEE Answers tool, I'm trying to make a particular column filter to only a specific type of record (as opposed to all records). In the Edit Column Formula Box, I go through the motions of creating the expression that looks like this:

      FILTER(SUM(CASE WHEN "Table"."Days Pending" > 0 THEN 1 ELSE 0 END) USING ("Table"."Special String" LIKE '%~33~%'))

      However, the column still provides all records, and not just records with a 33. Is there something special I'm forgetting for a column filter? It works if I add this as a report analysis; however, I need it to only apply to the column.



      Thx
        • 1. Re: Column filter help
          Srini VEERAVALLI
          This below condition is enough and to restrict to 33 just add add filter for the below expression not in 0
          FILTER("Table"."Days Pending" USING "Table"."Special String" LIKE '%~33~%')


          OR go by report level filter for 33

          If helps mark
          • 2. Re: Column filter help
            ZFMA86
            Thanks Srini. I can't use a report filter, as I'm only trying to filter a specific column.

            Also, I need to keep the SUM(CASE statement in the expression for what I am trying to count. If I use the expression you provided, I lose the ability to SUM.

            Any other advice?
            • 3. Re: Column filter help
              Srini VEERAVALLI
              Looks like "Table"."Days Pending" is not a metric, if it is metric may not required sum.

              Try this
              FILTER(SUM(CASE WHEN "Table"."Days Pending" > 0 THEN 1 ELSE 0 END by "Table"."Special String") USING ("Table"."Special String" LIKE '%~33~%'))

              Not sure how this works, if you provide sample data I might help.
              • 4. Re: Column filter help
                ZFMA86
                "Table"."Days Pending" is any number 1 to XXXXXXX.

                What I am trying to do is get an Avg of the days pending for all special status 33 records. So it should be simply adding the total days of records with a 33 status and dividing by the count of records with a 33 status.

                I should have stated this up front probably, but I wanted to test the Column Filter out on something simple. It just doesn't seem to be working! The Avg expression I am using is:

                FILTER(SUM(CASE WHEN "Pending Claim - Process Time"."Days Pending" > 0 THEN 1 ELSE 0 END by "Table"."Special Status String") / COUNT("Table"."Days Pending") USING ("Table"."Special String" LIKE '%~33~%'))
                • 5. Re: Column filter help
                  Srini VEERAVALLI
                  Try this
                  sum(case when "Table"."Special String" LIKE '%~33~%' and "Pending Claim - Process Time"."Days Pending">0 then 1 else 0 end)/COUNT("Table"."Days Pending")

                  If not helping; Try with rsum instead of sum, that would be top of report instead of by data by row wise.
                  • 6. Re: Column filter help
                    ZFMA86
                    The expression worked, but it gave me 0's on everything. I also tried RSUM and changing the aggregation totals around. Still no averages.

                    The expression you provided me doesn't seem to sum the actual days pending, but rather its summing the CASE statement (records with a 33).
                    • 7. Re: Column filter help
                      ZFMA86
                      I figured it out:

                      CASE WHEN "Table"."Special Status" LIKE '%~33~%' THEN SUM("Table"."Days Pending") END / CASE WHEN "Table"."Special Status" LIKE '%~33~%' THEN COUNT("Table"."Days Pending") END

                      This gives me the average while filtering only the column to the records with a 33.
                      • 8. Re: Column filter help
                        Srini VEERAVALLI
                        Good to know.

                        BTW: this is better

                        CASE WHEN "Table"."Special Status" LIKE '%~33~%' THEN
                        SUM("Table"."Days Pending") / COUNT("Table"."Days Pending")
                        END
                        • 9. Re: Column filter help
                          ZFMA86
                          Yes - thanks!

                          For the records that have a 0, how can I get it to put a 0 there instead of a blank?
                          • 10. Re: Column filter help
                            Srini VEERAVALLI
                            CASE WHEN "Table"."Special Status" LIKE '%~33~%' THEN
                            SUM("Table"."Days Pending") / COUNT("Table"."Days Pending")
                            ELSE 0
                            END

                            OR

                            ISNULL(CASE WHEN "Table"."Special Status" LIKE '%~33~%' THEN
                            SUM("Table"."Days Pending") / COUNT("Table"."Days Pending")
                            END,0)