This is the second time i am posting for the the same sample data, I got the answer last time but i want to implement it in a different way now. Here is the data :
student_ID, Exam_ID, Actual Date, Schedule Date,Status,Expected Output
6, 203, 12-07-2021, 12-07-2021,Done,NAV
6,202, 12-09-2021, -,Planned,DUP
6,200, 16-05-2015, 16-05-2015,Done,NAV
2,101,11-05-2021,11-05-2021,Cancelled,NAV
5,102,03-07-2021,03-07-2021, Done,NAV
5,109, 03-04-2022,-,Planned,DUP
3,207, 12-12-2000,12-12-2000,Done,NAV
I want to identify 'Student_ID' with more than 1 Exam_ID --> Get count of Exam_ID 's Per Student_ID
and
Check the duration between these Exam_ID's, i.e.,
For Example:
Student_ID,Exam_ID,Actual Date, Schedule Date,Exam Status
1,201,2011-11-11,2011-11-11,Done-Accepted -----------> Duration: 0 (Because there is no Exam_ID before this for this Student_ID)
1,289,2020-11-11,2020-11-11,Done ----------> Duration: Between 201 & 289 is 9*12 months
1,234,-,2021-11-11,PLANNED ------------> Duration: Between 289 & 234 is 12 months
I want to create a column for the duration. I don't know how to create a column which calculates the duration between the previous exam_ID based and not the First one(in this case 201).
After calculation, I want to show every Exam_ID whose duration is <=12 months and also the preceeding one, So that we know what is the previous exam_ID
the O/p for the given example, would look like below after implementing the logic.
To calculate duration, we need to consider the Actual date for the exam_id which has been marked as 'Done' otherwise 'Schedule date'. If it is cancelled we don't need to calculate duration. So the previous one is 'Cancelled' we should exclude it and just say''0' as duration.
I hope i am clear , Please help me with the logic. I have tried but could not do the duration logic.
Hello Everyone, I have try to implement the above but i have not succeded. yet, Any of your help would be helpful. The code i have used is below-
First to find if the student id is same or different from the before one, I have used previous() , Then if it is same , I am taking a different from the dates using iNTERVAL() function as follows, But it dint work.
Columns: student_ID, Exam_ID, Actual Date, Schedule Date,Status,Expected Output
If(Previous(student_ID)= student_ID,
Interval(Date- Previous(Date),'d'),'N');
Here the date field i have given the if else statement to which date to consider when, Like if the status is done, Then it considers Actual date else it considers Scheduled.
Besides your field names in your new calculation not matching your sample data, it looks ok to me and should match what you are saying. Maybe share your actual script and what results you are getting.
I used the file you sent on the last question along with this script and there is no error. So maybe you are using a different xlsx? Or you are using a different script.
@Manasareddy09 did you have another thread on this issue? I'm trying to find what I originally posted but I don't recall if that was in another thread?
But I updated your latest script to match field names in the latest xlsx attachment you sent and there are no errors. I think there is some cleanup still needed but I would rather look at what I actually sent originally.
[Sheet1]: LOAD [Exam_ID], [student_ID], [Status], Date([Schedule Date]) as [Schedule Date], Date([Actual Date] ) AS [Actual Exam Date], If(Isnull([Actual Date]) , [Schedule Date], [Actual Date]) as [Actual Date]
FROM [lib://DataFiles/Student_Exams.xlsx] (ooxml, embedded labels, table is Sheet1);
I have another thread for the issue but slightly different one, It is as follows .
I have a problem which I have been trying to solve from past few weeks but I could not get any answers. I have written the scenario below with a sample data set.
Please help me if you have some time. Your time would be much appreciated.
Data looks like below
Student_ID , Exam_ID , Date , Status, Expected Output
1,101, 12-01-2021, Done, -
2,102,12-02-2021, Done,-
2, 103,13-03-2022,'Cancelled',-
3, 104, 14-02-2015,'Done', -
3,105,14-03-2016,'Cancelled',-
3,106,16-02-2015, 'Done', 2 DAYS
3,107,16-02-2022,'Planned', 7 years
I want to see if there are Duplicate Exams which have been scheduled within 6,7,8 etc months (Depending on the user INPUT)
Right now , I have the following formula:
If(PEEK(Student_ID = Student_ID and Peek(Status)= Done and Status <> ‘Cancelled’ ,
Interval(Date - Peek(Date),’d') ) a output;
Order By Date
And then in the front end,
If(Date > Today(),
Output <= $(vPmonths)*30,
Output & ‘Days'
)
* $(vPmonths) is the input given by the user in the iNPUT VARIABLE CHART,
The output looks like below after sorting it by date in Pivot table
Student_ID , Exam_ID , Date , Status, Output
3,106,16-02-2015, 'Done', 2 DAYS
3,107,16-02-2022,'Planned', 7 years
1,101, 12-01-2021, Done, -
2,102,12-02-2021, Done,-
2, 103,13-03-2022,'Cancelled',-
3, 104, 14-02-2015,'Done', -
3,105,14-03-2016,'Cancelled',-
But I want to see all the entries of the students whose output is >=0, which looks like this
Student_ID , Exam_ID , Date , Status, Output
3,106,16-02-2015, 'Done', 2 DAYS
3,107,16-02-2022,'Planned', 7 years
3,105,14-03-2016,'Cancelled',-
The difference is that here I have all entries of Student_ID = 3 and nothing else.
I just want all the entries of the students if the output is >= 0. and all thier respective Exams
Please let me know if this is possible to do in Qliksense .
