Total the count of Attendees for last 7 days & last 14 days in Table View

I am looking to find a way in Beast mode to do the following

I have CustID and Class_Date and Class_Time. I am trying to show total Attendees and % of total for last 7 days and last 14 days both displaying in Table view mode. Any thoughts on how to do thiss via Beast Mode or any other way?

Find more posts tagged with

Accepted answers

guitarhero23

Some variation of the following should work for you.

Total Attendees: No beast mode necessary, just do a COUNT aggregation on any column (I used CustID)

Last 7 Days %:

SUM((CASE
WHEN DATEDIFF(CURDATE(),`Class_Date`) <= 7 THEN 1
ELSE 0
END
))

COUNT(`CustID`)

Last 14 Days %:

SUM((CASE
WHEN DATEDIFF(CURDATE(),`Class_Date`) <= 14 THEN 1
ELSE 0
END
))

COUNT(`CustID`)

Test data:

Dojo Help 601.JPG

Results:

Dojo Help 602.JPG

All comments

guitarhero23

Some variation of the following should work for you.

Total Attendees: No beast mode necessary, just do a COUNT aggregation on any column (I used CustID)

Last 7 Days %:

SUM((CASE
WHEN DATEDIFF(CURDATE(),`Class_Date`) <= 7 THEN 1
ELSE 0
END
))

COUNT(`CustID`)

Last 14 Days %:

SUM((CASE
WHEN DATEDIFF(CURDATE(),`Class_Date`) <= 14 THEN 1
ELSE 0
END
))

COUNT(`CustID`)

Test data:

Dojo Help 601.JPG

Results:

Dojo Help 602.JPG

user10528

Thanks, I needed something similar but it worked. I needed to Sum the total Customers for the last 7 & 14 days separately. However there are many classes in a day and I had to modify it slightly to work. But it gave me most of the answer...thank you!