Table Card Total Rows Incorrect

Reply
Green Belt

Table Card Total Rows Incorrect

I am trying to create a table card summarizing this data by Date and Department:RawData.png

 

Summarized the table looks like the following:
summary.png

 

How can I get the total rows to be correct on the card? I want the ability for the user to be able to drill down into a department and see a list of employees.

Thank you in advance for your assistance.


Accepted Solutions
Purple Belt

Re: Table Card Total Rows Incorrect

Ahh, I see what is happening here. You have duplicates in your dataset because of the employee id. You won't be able to achieve the result using total row as that is going to either sum or count the fields you have inputted. You will need to create a row in your dataset where you do the aggregation for grand total using a MySQL dataflow or Magic ETL.

 

You would aggregate it by date and put the "grand total" text under the department field and sum the max values of each department for a specific date, then union it to your final dataset.

 

Thanks,

 

Brian


**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.
Red Belt

Re: Table Card Total Rows Incorrect

I'll take a stab at this one...

 

Try creating new beast modes to calculate staffing level and number of employees

 

Staffing Level:

sum(staffing_level) / count(distinct ifnull(EmpID,1))

 

Number of Employees:

count(distinct ifnull(EmpID,0))

 

Then use these fields in your table instead of the current fields you are using.

 

You should then still be able to drill down and see EmpID if needed


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________

All Replies
Purple Belt

Re: Table Card Total Rows Incorrect

Hello,

 

I think you need to change your calculation to sum or count for staffing_level and no_employees by clicking the little pencil button when you hover.

 

Hope this helps,

 

Brian


**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.
Purple Belt

Re: Table Card Total Rows Incorrect

Ahh, I see what is happening here. You have duplicates in your dataset because of the employee id. You won't be able to achieve the result using total row as that is going to either sum or count the fields you have inputted. You will need to create a row in your dataset where you do the aggregation for grand total using a MySQL dataflow or Magic ETL.

 

You would aggregate it by date and put the "grand total" text under the department field and sum the max values of each department for a specific date, then union it to your final dataset.

 

Thanks,

 

Brian


**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.
Green Belt

Re: Table Card Total Rows Incorrect

Brian,

I could create and add total rows to the dataflow but then my card has no dynamic capabilities and any card filtering at the department level will not be reflected in the totals.  

 

Originally this card was summarized at the daily level and subtotals/totals worked fine but users are requesting the ability to drill down into the detail of each department and view employees in that total.

 

Any recommendations how I can summarize the card without losing the ability to drill down to the details?

 

Thank you,

Chris

Red Belt

Re: Table Card Total Rows Incorrect

I'll take a stab at this one...

 

Try creating new beast modes to calculate staffing level and number of employees

 

Staffing Level:

sum(staffing_level) / count(distinct ifnull(EmpID,1))

 

Number of Employees:

count(distinct ifnull(EmpID,0))

 

Then use these fields in your table instead of the current fields you are using.

 

You should then still be able to drill down and see EmpID if needed


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Green Belt

Re: Table Card Total Rows Incorrect

Getting closer but still a strange total on the staffing_level beast mode.

closer.png

Here are the beast modes I am using

staffing_level

sum(`staffing_level`)/count(distinct ifnull(`EmpID`,1))

 

number_employees

count(distinct `EmpID`) 

**  I had to remove the ifnull() because it was causing null records to be counted as 1 **

Purple Belt

Re: Table Card Total Rows Incorrect

You could try this ... the only issue will be is if two department staffing levels are the same, then it will calculate incorrectly.

 

sum(distinct `staffing_level`)/count(distinct `EmpID`)

 

Thanks,

 

Brian


**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.
Highlighted
Purple Belt

Re: Table Card Total Rows Incorrect

Another alternative would be a window function. I have never actually tried to use this in beastmode but it would look something like ... 

 

 

CASE WHEN `EmpID` IS NULL
THEN staffing_level
ELSE SUM(distinct staffing_level) OVER (PARTITION BY department)
/count(distinct `EmpID`)
END

Thanks,

 

Brian

 


**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.
Purple Belt

Re: Table Card Total Rows Incorrect

Wait a second,

 

Why are we trying to divide staffing level? Is it just supposed to be a sum of total employees needed?

 

in this scenario, you can just do SUM(DISTINCT staffing_level)

 

Thanks,

 

Brian


**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.
Green Belt

Re: Table Card Total Rows Incorrect

Brian,

 

sum(distinct `staffing_level`) will not work correctly if two departments have the same staffing level.  We are attempting to sum all rows and divide it by the number of employees.  This works for each row but the grand total row for some reason is not a whole number.

 

In this example using the Main Bar department the sum(`staffing_level`) = 12 and count(distinct `empID`) = 3 and 12/3=4 which is the number I expect. This does not explain why the total row is incorrect on the table where it should be adding 4+6+2 = 12 but is displaying 4.57??

Announcements
Call for Presenters! We want to hear your Domo story, submit your entry and win a free pass to Domopalooza 2019!. Click here!