Compare data with previous day

[#1]

Hi,

I would like to compare the data of the currently selected date with the data from the previous day.

Date	Name	reference
07/01/2012	Jane Z	DH344
08/01/2012	Jane Z	ED322
07/01/2012	Carol P	PK123
08/01/2012	Carol P	PK123

I want a table that will only show the Date, Name and reference of the selected date. But I want to formatt the reference text if the reference changed from the previouse date. The table below shows how I want my data to look

Date	Name	Reference
08/01/2012	Jane Z	ED322
08/01/2012	Carol P	PK123

I havent been able to find a current disscussion on this that doesnt include set analysis and seeing as I am not performing aggregation to the Reference field I didnt think set analysis was appropriate for this.

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.

[#3]

Thankyou it worked perfectly!!

Thankyou for being so fast too!!

[JianangZhao]

hi Wassenaar,

i have one data table has date and deal_number two columns,  and some sample data like below. There are many values for the date field  and deal_number, i just put two days data as sample.

for the adjacent two days, i want to compare the deal_number, if the deal_number only exists in the date 05-06, the record will be noted as "NEW", if the deal_number exists in both date 05-06 and 05-05 will be noted "FORWARD", and if the deal_number only exists in the date 05-05, the record will be noted as "OLD", and for all date, i need achieve this compare. 

date	deal_number
...	...
2019-05-06	a001
2019-05-06	a002
2019-05-06	a003
2019-05-05	a004
2019-05-05	a003
2019-05-05	a005
...	...

The expected result likes, i hope i have made it clear, and is there any suggestion on this? thanks a lot

date	deal_number	Result
...	...
2019-05-06	a001	NEW (only exist in 6-6)
2019-05-06	a002	NEW (only exist in 6-6)
2019-05-06	a003	FORWARD (exist in both days, 6-5 and 6-6)
2019-05-05	a004	OLD (only exist in 6-5)
2019-05-05	a003	FORWARD
2019-05-05	a005	OLD (only exist in 6-5)
...	...