Highest count in a week

[#1]

Hi all,

i have a two tables coursecompletion and signup activitylog.

coursecompletion table:                                     

course_completed                    Date

0                                      10/05/2015

1                                       10/05/2015

0                                      10/05/2015

1                                       10/05/2015

1                                       11/05/2015

1                                       11/05/2015

1                                        11/05/2015

0                                        11/05/2015

1                                        12/05/2015

1	12/05/2015
1	12/05/2015
1	12/05/2015
1	12/05/2015
1	12/05/2015
1	12/05/2015

signupactivitylog Table:

user_signed                      Date

0                                      10/05/2015

1                                       10/05/2015

0                                      10/05/2015

1                                       10/05/2015

1                                       11/05/2015

1                                       11/05/2015

1                                        11/05/2015

0                                        11/05/2015

1                                        12/05/2015

1	12/05/2015
1	12/05/2015
1	12/05/2015
1	12/05/2015
1	12/05/2015
1	12/05/2015

from the above two tables i need in which day count of the user_signed and course_completed is more.

Thanks,

Pramod

[jagan]

Hi,

Try like this

coursecompletiontable:

LOAD

course_completed,

Date(Date#(Date, 'MM/DD/YYYY')) AS Date

INLINE [                                   

course_completed,                    Date

0,                                      10/05/2015

1,                                       10/05/2015

0 ,                                     10/05/2015

1,                                       10/05/2015

1,                                       11/05/2015

1,                                       11/05/2015

1,                                        11/05/2015

0,                                        11/05/2015

1,                                        12/05/2015

1,                                   12/05/2015

1, 12/05/2015

1, 12/05/2015

1, 12/05/2015

1, 12/05/2015

1, 12/05/2015];

For getting maximum course_completed  date use below expression

=Date(FirstSortedValue(Date, -Aggr(Sum(course_completed), Date)))

The same way do this for User signed.

Regards,

jagan.

[#3]

hi jagan mohan,

after using the above expression in measure i got the below result.

Thanks,
Pramod

[jagan]

Hi,

Can you attach sample file and your expected output, so that it would be easier to understand your requirements.

Regards,

Jagan.

[#5]

hi Jagan,

PFA of sample data.

Thanks,

Pramod

[jagan]

Hi,

You need to use this in text object so that you will get the date which has max Course completions.

Regards,

Jagan.

[#7]

Hi jagan,

Din't get you. would you please explain it briefly.

Thanks,

Pramod

[jagan]

Hi,

What is your expected output for the given data?

Regards,

Jagan.

[#9]

i need the day in which i have more count of completions?

by using the  expression Sum(course_completed) in measure. i am getting the count of course completion of all the day.

but in that i need highest course completion day in last week.

for example:

date                                       completion count

11/05/2015                                 30

12/05/2015                                 25

13/05/2015                                 20

14/05/2015                                 35

15/05/2015                                 15

16/05/2015                                  27

here date 14/05/2015  has highest count. i have to display that day in bar graph.

i think you have got it.

Thanks,

Pramod

[jagan]

Hi,

Try like this

Script:

Data:

LOAD course_completion_id,

     course_completed,

     date_completed

FROM

[MOCK_DATA(4).csv]

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

Barchart:

Dimension : date_completed

Expression: Sum(course_completed)

Sort Tab : Select Y Value-> Descending

Dimension Limits Tab : Select "Restrict which values are displayed using the first expression" option

and select Show Only Largest  and give 1 in Values text box.

Hope this helps you.

Regards,

Jagan.