4 Replies Latest reply: Jul 27, 2015 8:25 PM by Andrew Noble RSS

    IF statement in expression - applying Min(IF()) to field

    Andrew Noble

      Hi

       

      I have a list of tariffs that have a 'begin date' and 'end date' and thus a tariff duration (the difference between the two dates).

       

      The required tariff is the one that falls between the begin and end dates, with the lowest duration.

      i.e. there may be tariffs set from 1 Jan to 31 Jan, but if there is another tariff set for 4 Jan to 5 Jan, then this tariff will override the whole month duration.

       

      So, I need an expression that can return the tariff for each of the dates in calendar date dimension, where the tariff duration is the minimum for that date.

       

      This expression will give me the minimum duration which I put into the first expression - Column(1):

       

      Min(

          If (

                KeyDate >= BEGIN_DATE and

                KeyDate <= END_DATE

          , TARIFF_DURATION))

       

      This next expression needs to use the minimum tariff duration determined from the above expression to return the average tariff with that duration:

      Avg(
            If (
                 KeyDate >= BEGIN_DATE and
                 KeyDate <= END_DATE and
                 TARIFF_DURATION = Column(1)

      , TARIFF))


      But I can't get this to work.

       

      Ideally, I would like to do the whole expression in one step.

       

      Any help would be appreciated.

       

       

      EDIT:

       

      Refer attached sample file that shows a simplified version of what I am trying to achieve.

      • The "Average Tariff" averages all tariffs that there is a date range for - not what I want
      • The Min Tariff Duration is what I need to filter by
      • The third expression is the one that I need to work. I have tried to refer to the column that has the minimum tariff duration.

       

      Message was edited by: Andrew Noble