Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Message was edited by: Andrew Noble
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
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.
Sunny, I have attached a sample file in the original post. I would appreciate your thoughts.
Jonathan, I have attached a sample file in the original post. I would appreciate your thoughts.