6 Replies Latest reply: Jan 11, 2012 5:34 PM by Pierre Philippon

# Set Analysis - Getting previous day data

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.

• ###### Set Analysis - Getting previous day data

use below code

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

hope this helps

• ###### Set Analysis - Getting previous day data

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

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

Many thanks

• ###### Set Analysis - Getting previous day data

any how you finally have your solution

• ###### Set Analysis - Getting previous day data

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

• ###### Set Analysis - Getting previous day data

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)

• ###### Set Analysis - Getting previous day data

Thank you so much, it works perfectly.

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