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

[#1]

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

[sunny_talwar]

Would you be able to provide some sample data to work with???? It would be easier to approach the problem with actually testing the stuff out.

Best,

Sunny

[jonathandienst]

You could land up with very poor performance doing this sort of processing in the front end. I would explore adding some processing to the load script. I assume that the 'correct' tariff is not subject to user selections, and if that is the case, you could calculate the tariff during the load process.

You would probably need a master calendar with all the dates in the date range of interest and you could apply the product names and tariffs to the calendar using an interval match, creating a date/product/tariff table with the tariff 'from' and 'to' dates. You could then flag the tariffs that have the lowest duration and you could use a set expression in the front end to select the 'correct' tariffs.

I can only give these general guidelines unless you can post some sample data and your qvw.

[#4]

Sunny, I have attached a sample file in the original post. I would appreciate your thoughts.

[#5]

Jonathan, I have attached a sample file in the original post. I would appreciate your thoughts.