Count Student IDs who had a total number of credit hours >0 by x week of the semester

[Edge]

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())))

[Anil_Babu_Samineni]

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)))

[Edge]

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)

)

[sunny_talwar]

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?

[Edge]

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 .

[Edge]

Hello Everyone, 

Anybody have any idea how can I accomplish this?

[Brett_Bleess]

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

[sunny_talwar]

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

[sunny_talwar]

More on The As-Of table can be read by checking out the link.

[Saravanan_Desingh]

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