Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I have the below dataset and I am trying count the unique student IDs who had a number of credit hours > 0 by that week.
I have the below expression but it is not working:
RangeSum(Above(Count(DISTINCT aggr(if(sum(UNT_TAKEN_MOD)>0 and DTTM_STAMP_SEC <= max(DTTM_STAMP_SEC),EMPLID),EMPLID) ),0,RowNo()))
Attached is a screenshot of my dataset.
and below is the chart and expression for the cumulative credit hours sold by semester week which I created successfully.
if(sum(UNT_TAKEN_MOD)=0,Null(),RangeSum(Above(sum(UNT_TAKEN_MOD),0,RowNo())))
Check this way?
RangeSum(Above(Sum(Aggr(Count({<UNT_TAKEN_MOD={">=0"}, DTTM_STAMP_SEC ={"<=$(=Max(DTTM_STAMP_SEC ))"}, [credit hours]={">0"}>} Distinct EMPLID), EMPLID)), 0, RowNo(TOTAL)))
Dear Anil,
Many thanks for your response. I am afraid I did't fully explain my problem. So after looking at your expression, I have come down with this expression.
I think I can rephrase what I am trying to do this way. For a given week how many distinct enrolled students are there from the enrollment and enrollment of this week and previous weeks.
In other words, count distinct student ID(Employee ID), if the sum off all of the transactions of the students (sum(unt_taken_mod)) is greater than 0, given that these transactions happened before the last date of this week.
I tried the below, but it is not working.
=
Count(DISTINCT
Aggr(if(sum( {< DTTM_STAMP_SEC ={"<=$(=Max(Total<RELATIONINWEEKS> DTTM_STAMP_SEC ))"}>} UNT_TAKEN_MOD)>0,EMPLID), EMPLID)
)
I am not entirely sure I follow what you are trying to get? Would you be able to share 10-15 rows of raw data and explain what you are looking to get as an output based on the sample provided?
Dear Sunny,
Many thanks for your response. Allow me to better explain my problem. Below is a sample dataset:
Student ID | Course | Action | Credits | Action Date |
1002020 | MTT100 | E | 3 | Tuesday, 1 January, 2019 |
1053220 | MTG101 | E | 3 | Tuesday, 1 January, 2019 |
1002020 | MTT100 | D | -3 | Wednesday, 2 January, 2019 |
1068523 | MTG101 | E | 3 | Wednesday, 2 January, 2019 |
1077777 | MTT100 | E | 3 | Thursday, 3 January, 2019 |
1053220 | MTG101 | D | -3 | Thursday, 3 January, 2019 |
1053220 | MTR500 | E | 3 | Thursday, 3 January, 2019 |
Desired Output is the below.
Date | Total Cumulative Credit Hours | Enrolled |
Tuesday, 1 January, 2019 | 6 | 2 |
Wednesday, 2 January, 2019 | 6 | 2 |
Thursday, 3 January, 2019 | 9 | 3 |
So, CHS is the sum of all transactions up to a certain date, which I have completed with no issue.
Enrolled is the issue. Because it is the distinct count of student IDs whose cumulative number of credit hours is greater than 0 on a given date.
So, in my above sample data by end of 3 Jan 2019 students 1077777 and 1068523 enrolled and never dropped. 1002020 enrolled and dropped before 3 Jan 2019. 1053220 enrolled on Jan 1st, then on Jan 3 dropped but enrolled in another course.
Hope this makes clear my issue.
Many thanks for your help in advance .
Hello Everyone,
Anybody have any idea how can I accomplish this?
Just leaving a post to kick this back up in the list to see if @sunny_talwar or @Anil_Babu_Samineni can circle back to have another look at things. given your post with some data and your desired output...
Regards,
Brett
Apologies for not getting back earlier @Edge . Try doing this using AsOfTable....
Script
Table:
LOAD *,
Date(Date#(Trim(Mid([Action Date], Index([Action Date], ',') + 1)), 'D MMMM, YYYY'), 'D MMMM, YYYY') as Date;
LOAD * INLINE [
Student ID, Course, Action, Credits, Action Date
1002020, MTT100, E, 3, "Tuesday, 1 January, 2019"
1053220, MTG101, E, 3, "Tuesday, 1 January, 2019"
1002020, MTT100, D, -3, "Wednesday, 2 January, 2019"
1068523, MTG101, E, 3, "Wednesday, 2 January, 2019"
1077777, MTT100, E, 3, "Thursday, 3 January, 2019"
1053220, MTG101, D, -3, "Thursday, 3 January, 2019"
1053220, MTR500, E, 3, "Thursday, 3 January, 2019"
];
Max:
LOAD Min(Date) as MinDate
Resident Table;
LET vMinDate = Peek('MinDate');
AsOfTable:
LOAD Date as AsOfDate,
Date(Date, 'WWWW') & ', ' & Date as AsOfActionDate,
Date - IterNo() + 1 as Date,
-IterNo() + 1 as Diff
Resident Table
While Date - IterNo() + 1 >= $(vMinDate);
And then a chart with
Dimension
AsOfActionDate
Expressions
=Sum(Credits)
=Count(DISTINCT Course)
Thanks @Brett_Bleess for bringing this back on our radar
More on The As-Of table can be read by checking out the link.
One solution is.
tab1:
LOAD *, Date(Date#(Trim(Mid([Action Date], Index([Action Date], ',') + 1)), 'D MMMM, YYYY')) As ActDate
;
LOAD * INLINE [
Student ID, Course, Action, Credits, Action Date
1002020, MTT100, E, 3, "Tuesday, 1 January, 2019"
1053220, MTG101, E, 3, "Tuesday, 1 January, 2019"
1002020, MTT100, D, -3, "Wednesday, 2 January, 2019"
1068523, MTG101, E, 3, "Wednesday, 2 January, 2019"
1077777, MTT100, E, 3, "Thursday, 3 January, 2019"
1053220, MTG101, D, -3, "Thursday, 3 January, 2019"
1053220, MTR500, E, 3, "Thursday, 3 January, 2019"
];
tab2:
LOAD Concat(DISTINCT Chr(39)&ActDate&Chr(39),',',ActDate) As ActDts
Resident tab1;
Let vActDts=Peek('ActDts');
Drop Table tab2;
For Each vActDt In $(vActDts)
Trace *** vActDt=$(vActDt);
tmp:
LOAD '$(vActDt)' As ActDate, Credits, [Student ID], Course
Resident tab1
Where ActDate<='$(vActDt)'
;
Left Join(tmp)
LOAD '$(vActDt)' As ActDate, Concat(DISTINCT Action) As ActStr, [Student ID], Course
Resident tab1
Where ActDate<='$(vActDt)'
Group By [Student ID],Course
;
tmp2:
LOAD '$(vActDt)' As ActDate, Sum(Credits) As Sum_Cre, Count(DISTINCT [Student ID]&Course) As Cnt
Resident tmp
Where ActStr='E'
;
Drop Table tmp;
Next