Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am trying to select a value from the a PriceDt field using below the below method.
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
Hi Deepak,
Check whether the below code helps you.
sum(if(PriceDt=(max(total if(PriceDt <= vFromDate, (PriceDt)))),Price))
- Sridhar
Hi Sridhar,
Thanks for your Quick reply. But its not working. Now all data is coming blank.
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
Sry here is the app.
- Sridhar
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
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
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
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
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.