Scenario Logic Help

[abhaysingh]

Hi All,

I have two tables Community Test Data Assign and Community Test Data Assign. there are link on the basis of Visit ID,

In assign Table i have Date Submitted Field and in Visit Table i have Date Due field.

I need to calculate below 3 Logic..

1. AssignmentsCompletedCount =  count of DateSubmitted >= startDate And DateSubmitted <= endDate and DateSubmitted <= Visit.DateDue

2. AssignmentsSubmittedLateCount = Count of DateSubmitted >= startDate And DateSubmitted <= endDate and DateSubmitted > Visit.DateDue

3. AssignmentsOverdueCount = Count of Visit.DateDue + Visit.GracePeriod >= startDate && Visit.DateDue + Visit.GracePeriod <= endDate && DateSubmitted == null

Start Date and End Date user will enter..

I need the count of Assignment Completed, Assignement Submitted late, Assignment Overdue in Pie chart..

Can any one help me in this??

thanks

[Anonymous]

Also check whether DataSubmitted is null or a blank value!!!

This may mismatch the values of OverDue

[Anonymous]

AssmntOverdueDate??

How you are creating?? this may be the another reason of your mismatch of values...

[Gabriel]

Hi,

May be this will help. I created the extra field in the script as Task Status then in the UI just use SET ANALYSIS to count VisitId  where Task Status = 'Submitted In Time' or 'Late Submission' or 'Task OverDue'

TestAssigned:

LOAD VisitId,

     DateSubmitted,

     SurveyTaskId,

     IsDeleted

FROM

[Community Test Data Assgn.xlsx]

(ooxml, embedded labels, table is Sheet2);

LEFT JOIN(TestAssigned)

LOAD

     VisitId,

      DateDue

FROM

[Community Test data visit.csv.txt]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Final_Task_Data:

load

*,

IF(DateSubmitted <= DateDue,     'Task Submitted In Time',

        IF(DateSubmitted > DateDue , 'Late Submission',

            IF(ISNULL(DateDue) OR LEN(DateDue) = 0, 'Task OverDue')))            AS [Task Status]

Resident TestAssigned

;

DROP Table TestAssigned;

Hope this helps