How to get this output below scenario.

[Naps_230]

Hi Team, 

Could you please help me on that below scenario. i need this achieve output only qlik sense frontend not backend.

NEEDD SHOW output column in frontend on based of below condition:

1. last day absent, then Yes populate
2. previous date absent, then Yes populate
3. if any attended in between then don’t populate yes for previous absent.

need same result below format.

Student	Date	Status	output
A	2/1/2022	Absent
A	3/1/2022	Attended
A	4/1/2022	Absent	Yes
A	5/1/2022	Absent	Yes
B	2/1/2022	Attended
B	3/1/2022	Absent
B	4/1/2022	Absent
B	5/1/2022	Attended
B	6/1/2022	Absent	Yes
C	2/1/2022	Absent
C	3/1/2022	Absent
C	4/1/2022	Absent
C	5/1/2022	Attended

[justISO]

Hi, not so elegant, but you can use something like this:

if(date(Date)=date(aggr(nodistinct max(Date), Student)) and Status='Absent', 'Yes',
if(date(below(total Date))=date(aggr(nodistinct max(Date), Student)) and Below(total Status)='Absent' and Status='Absent', 'Yes' ))

but this works only for 1 date below comparison. Depends on how big is the data scope, you probably need to add more 'level' and compare below(below... In script level (backend) this would not be needed, as peek and previous functions could help in this case.

[vinieme12]

Can you explain why for Student B - 3/1/22 and 4/1/22 are not marked absent but for A 4/1 and 5/1 are ?

[Naps_230]

Hi,

 Student B have Attended on  5/1/22 in between, that's why 3/1/22  and 4/1/22 are not marked, but A doesn't have.

[vinieme12]

Still doesn't makes sense 

student A also  has Attended on 3/1 which is between 2/1 and 4/1 ,   so why is 4/1 marked yes?

And for B why is 6th marked Yes 

Can you explain in the table itself what conditions to validate??

[CELAMBARASAN]

Hi 

Please check with the below expression for the desired result

=if(FirstSortedValue(Total<Student> Status, -1*Date)='Absent' and Date>= Max(Total <Student> Date,2) and Status = 'Absent','Yes')

Thanks

Celambarasan

[Naps_230]

Hi ,

I need this below output.

Student	Date	Status	output
A	2/1/2022	Absent
A	3/1/2022	Attended
A	4/1/2022	Absent	Yes
A	5/1/2022	Absent	Yes
A	6/1/2022	Absent	Yes
B	2/1/2022	Attended
B	3/1/2022	Absent
B	4/1/2022	Absent
B	5/1/2022	Attended
B	6/1/2022	Absent	Yes
C	2/1/2022	Absent
C	3/1/2022	Absent
C	4/1/2022	Absent
C	5/1/2022	Attended
D	2/1/2022	Attended
D	3/1/2022	Absent	Yes
D	4/1/2022	Absent	Yes
D	5/1/2022	Absent	Yes
D	6/1/2022	Absent	Yes

[Naps_230]

Hi , 

we follow this below condition.

1. if last date of each student group have status absent, then Yes populate
2. if previous date status is absent, then Yes populate
3. if any status is attended in between absent for each student then don’t populate yes for previous absent.

[Naps_230]

Hi ,

Also we follow this below condition.

1. if last date of each student group have status absent, then Yes populate
2. if previous date status is absent, then Yes populate
3. if any status is attended in between absent for each student then don’t populate yes for previous absent.

[CELAMBARASAN]

Hi,

Tweaked the expression further for your requirement.

=if(FirstSortedValue(Total<Student> Status, -1*Date)='Absent' and Date>= FirstSortedValue(Total <Student> {<Status-={'Absent'}>} Date, -1*Date) and Status = 'Absent','Yes')

Hope this helps

Regards

Celambarasan