Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good morning
I have a table with DateD and Price fields.
In a straight table, I would like to get the Price for current selected DateD, but also for the previous DateD
I manage to get the Previous DateD value with the formula =Date(Only(DateD)-1) or even =FiedlValue('DateD', FieldIndex('DateD',DateD)-1)
I also manage to get the Prcie at specified DateD, for exemple =Sum({$<DateD={'02/11/2011'}>} Price)
But I do not manage to aggregate the two formulas to get the Price at the DateD before the selected DateD.
I am also unsure if there is a way not to use the Sum function, as what I really want is a field value, not a sum, but as there is only one value, the sum eqauls the value, so it does not really matter. But still maybe there is a better function than the sum I am using.
Many thanks for your help.
use below code
sum({$<Day={"$(=only(Day)-1)"}>}Price)
hope this helps
sum({$<Day={"$(=only(Day)-1)"}>}Price) does not work but
sum({$<Day={"$(=Date(only(Day)-1))"}>}Price) does work perfectly.
Many thanks
any how you finally have your solution
Actually, this does work but only if the date are following with no missing date like weekend of Bank Holiday
If Day is a Monday, Date(only(Day)-1 will be a Sunday, while my Set of Day is Mon-Fri days. I would like to retrieve the Day before in my Set, which is the Friday.
Any idea?
Many thanks
If you Set of Day really misses some values inbetween for the weekend, try something like
=sum({<Day= {'$(=Date(max({<Day = {"<$(=max(Day))"}> } Day)))'}>} Price)
Thank you so much, it works perfectly.
Do you think using the Previous function in the script would be easier?