Set Analysis - Getting previous day data

[Anonymous]

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.

[SunilChauhan]

use below code

sum({$<Day={"$(=only(Day)-1)"}>}Price)

hope this helps

[Anonymous]

sum({$<Day={"$(=only(Day)-1)"}>}Price) does not work but

sum({$<Day={"$(=Date(only(Day)-1))"}>}Price) does work perfectly.

Many thanks

[SunilChauhan · Accepted solution]

any how you finally have your solution

[Anonymous]

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

[swuehl]

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)

[Anonymous]

Thank you so much, it works perfectly.

Do you think using the Previous function in the script would be easier?