5 Replies Latest reply on Mar 7, 2015 12:38 AM by user13308964

    Unable to filter data using calculated column

    user13308964

      hello experts....

       

       

      I am having an issue when filtering a report on a Cumulative calculated column.

      I have a pivot table view, in which i have created a calculated column on the totals.

       

      Then I use this Calculated column - called cumulative totals, and do a conditional formatting. (giving different colors for 3 different range).

      After that I create a dashboard filter on the cumulative totals based on the range. After that I created a report which shows 13 month data for a selected month, but when i try to filter on cumulative totals, the monthly data is also getting filtered out.

       

      e.g: If I filter cumulative total for Range above 90%, all the individuals months below 90% doesnt show up.

       

       

      I just want to filter to be applied on the cumulative total... not on the individual months.

       

      Is there any way i can achieve? Pls advise

        • 1. Re: Unable to filter data using calculated column
          user13308964

          Hello  Experts.. Any suggestions?

          • 2. Re: Unable to filter data using calculated column
            Gianni Ceresa

            Hi,

            Not sure I got exactly what you try to do ....

            You want to display a measure over 13 months but the measure must be there only if the total of the 13 months is above 90% (of what?).

            And what you currently see is that the filter "above 90%" happen at the month level instead of the "all the 13 months" level.

            Is that your situation?

            I would say your filter need to be a formula giving you the 13 months total instead of the monthly value and your problem is fixed. ("Aggregate at" or any kind of similar formula)

            • 3. Re: Unable to filter data using calculated column
              user13308964

              Hi Thanks for taking time to respond...

               

              Yeah you got it right.

              So here s my situation,.. I have a report.. where user picks a particular month.. and based on the Month selection... the report displays a Pivot table for prior 13 months.. So I pick Oct 2014 on the Dashboard.. my report shows metrics from prior 13 month onwards.. ie oct 2013, nov 2013, de... so on till Oct 2014. For all this metrics. I have displayed the totals. And I have created another Column called Cumulative Total for these 13 months. And I apply a dashboard filter this cumulative Column. But, when i apply the filter that data is getting filtered at month level..

               

              I din get your solution,..so how can i create a filter on the 13 months total... (these 13 months are calculated in a hidden report which takes the user input as the start date.. and gives prior 13 month date using SQL_TSI function.. then I am applying filter - where month in between start data and prior 13 month date - in the original report). .. ?

               

              Anyway, Let me try and update you..

               

              Thanks again.. Have a gud day ahead!

              • 4. Re: Unable to filter data using calculated column
                Gianni Ceresa

                How are you calculating the "Cumulative Total" for the 13 months?

                 

                PS: isn't it a little "too much" to use a filter based on another analysis just to get the 13 months? A simple BETWEEN based on the selected period and a calculation to go 13 months ago would do the same but in the analysis itself...

                • 5. Re: Unable to filter data using calculated column
                  user13308964

                  1. I do not have any specific formula for the Cumulative total. Just the Avg of the 13 months total.

                  2. Sorry I am not an expert... how can you do that? I did try calculating the prior 13 month as a column in the report. but how can you pass this as a variable for the between filter? so my filter will be  .. Month between @{STMTH}{OCT-2014} and ??? . (BTW I do not have hierarchy defined yet).

                   

                  thanks for your time