I am calculating the patients who are visited again and so i had created flag in script and called in set analysis.
If(Peek(patientid)=patientid,1,0) as revisit
But i am not getting the answer. In this case my answer should be 2.
i tweaked the script a bit. Expression is same as yours
patientid, type, date, revenue, discount
1, Op, 2/3/2017, 5000, 250
2, Op, 5/3/2017, 4500, 0
3, Op, 18/4/2017, 6000, 0
4, Op, 23/4/2017, 7000, 350
5, Op, 1/5/2017, 2000, 0
1, Op, 1/5/2017, 3000, 150
4, Op, 2/5/2017, 5000, 250
If(patientid=Previous(patientid),1,0) as revisit
ORDER BY patientid ASC;
drop table ABC;
If(patientid=Previous(patientid),1,0) as revisit // But, In your data there is not common with current to previous
i think your data needed to be sorted by Patient iD like Siddarth said.
try this script to test that:
If(patientid=Previous(patientid),1,0) as revisit,
ORDER BY patientid ASC
date as Date,
(ooxml, embedded labels, table is Sheet1);
Sort it by patient id and then date.
Unless there is a reason you are sorting it by date first.
You would have to try a different approach if you have to sort the data by date first.