Help in If statement

[deepakk]

Hi All,

I am trying to select a value from the a PriceDt field using below the below method.

if(PriceDt=(max(if(PriceDt <= vFromDate, (PriceDt)))),Price)

It works fine when the list contains one value for PriceDt but when there are multiple values

the if statement fails.

Let me know how i can resolve it.

Thanks

Deepak

[#2]

Hi Deepak,

Check whether the below code helps you.

sum(if(PriceDt=(max(total if(PriceDt <= vFromDate, (PriceDt)))),Price))

- Sridhar

[deepakk]

Hi Sridhar,

Thanks for your Quick reply. But its not working. Now all data is coming blank.

[#4]

Hi,

Check this application. i used this application to develop that code.Its working for me.

Check and let me if it works. Else post your sample here and please explain your scenario.

- Sridhar

[#5]

Sry here is the app.

- Sridhar

[deepakk]

Hi Sridhar,

Please check my application. I need to create Opening balance for each month based on the FromDate.

I am getting the Qty right. I need to pick up the Rate which is in different table and shoukd be the less than the fromDate and maximum of the result.

I you see the inventory sheet I am getting the Max date for each item right but the rate is not been picked for every item. for certain its coming zero.

Let me know if you have solution for this.

Thanks

Deepak

[#7]

Hi Deepak,

Sry, Your application is very wiered and i cnat able to understand any thing.

check whether my understanding is correct.

You want to take the max of date from the date which is less than the VfromDate. Rite...?

- Sridhar

[deepakk]

Yes I need to get the maxdate and its respective price .

When you get the maxdate from the field PriceDt, check the Price field and need to get that Price.

@Deepak

[#9]

Hi Deepak,

My attachment which is in previous post will do the same.

For you clarification, i have added few more dates in my application and attaching the same application here.

I use the let vFromDate=addmonths(today(),-4); i.e VFromDate = 17/2/2009

if i take all dates which is less or equal to this date, which would be the following dates.

1/1/2009, 1
1/2/2009, 2
2/2/2009, 4

From this the maxmum would be 2/2/2009 and the respective value is 4.

you can see the same 4 in my attached application.

- Sridhar

[deepakk]

Hi Sridhar,

If you add different items to that the situation changes. Currently its picking up the total, but when there are different items , their max Date is different

hence values become zero when I use your expression.