1 Reply Latest reply on Jul 28, 2016 3:01 PM by Stoyko Kostov

    Using the Filter selection to define a Calculated Field

    Donald Wilson

      Hi group,

       

      I would like to know if the following can be done (I'm using the Professional Edition Version 9.0):

       

      I have 2 fields that are filtered in my table.  These are called "Weeks" and "Weeks2".  The are dates that have been formatted to display the date of the first day of each week.  "Week" represents 2016 and "Week2" is for 2015.  Both filters allow the user to select "All" or single/multiple dates.

       

      Based on what the user selects, I'd like to create a calculated field using either CASE or IF statements.  

       

      If the user selects a date (or multiple dates) via the "Weeks" filter AND "All" is selected for the "Weeks2" filter, this new calculated field will do specific calculations from one set of fields. 

       

      If the user selects "All" via the "Weeks" filter AND a date (or multiple dates) is selected for the "Weeks2" filter, this new calculated field will do specific calculations from a second set of fields.

       

      Is this possible?  I've tried this a couple of ways with no success.  What I have tried is:

       

      IF [Weeks]<>'All' AND [Weeks]='All'  THEN SUM([RMS_2016])-SUM([RMS_2015])

      END

      IF [Weeks]='All' AND [Weeks]<>'All'  THEN SUM([RMS_2015])-SUM([RMS_2014])

      END

       

      Is this something that is possible?  If so, what must I do?

       

      In advance, thanks for your help.

       

      Don