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

user10528

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?

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:

 

Results:

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:

 

Results:

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!