topic Set Analysis - Getting previous day data in QlikView

Set Analysis - Getting previous day data

Anonymous — Thu, 22 Dec 2011 11:16:23 GMT

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.

Set Analysis - Getting previous day data

SunilChauhan — Thu, 22 Dec 2011 11:22:03 GMT

use below code

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

hope this helps

Set Analysis - Getting previous day data

Anonymous — Thu, 22 Dec 2011 11:26:55 GMT

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

SunilChauhan — Thu, 22 Dec 2011 11:29:27 GMT

any how you finally have your solution

Set Analysis - Getting previous day data

Anonymous — Wed, 11 Jan 2012 21:54:25 GMT

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

swuehl — Wed, 11 Jan 2012 22:19:35 GMT

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

Anonymous — Wed, 11 Jan 2012 22:34:41 GMT

Thank you so much, it works perfectly.

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