Issue in Pivot table

[bhuprakash]

Dear All,

I am attaching the dashboard and in the "Average Reporting per day" Data should reflect like below -

Row Labels	SA-I	SA-II	Grand Total
Below 3	104	138	242
More than 3	15	30	45
Grand Total	119	168	287

But in my dashboard only total values are coming. So request you to correct it.

2- I have employee details in Activity counts file and Activity done data in ActivityDetails_Master file. "Average Reporting per day" and

"working days per user" is giving correct data but it is failing is a employee has not done any activity because ActivityDetails_Master table has only employee details who have done activity. so how can i include those employee in the pivot.

[devarasu07]

Hi,

Try to use like below in your back end script,

used preceding load for logical calculated field (average_reporting_days)

activity_days:
Load *,
if( User_Days > 25, '>25',
      If(User_Days > 20, '21-25',
      if(User_Days > 10, '11-20',
      if(User_Days > 0, '1-10', '0')))) as Bucket,

if( average_reporting > 3, 'More than 3',
      if( average_reporting = 3, '3',
      if( average_reporting < 3, 'Below 3', '0'))) as average_reporting_days;

LOAD emp_id,count(DISTINCT Activity_Date) as User_Days,
  count(DISTINCT Activity_Date)/25 as average_reporting   
RESIDENT activity_master Group By emp_id;

Thanks,

Deva

[bhuprakash]

Hi Deva,

It is giving same result like before.

[devarasu07]

hi,

can you share your source file  "ActivityDetails_master.xlsx"

Uploading a Sample

[lnimeshkrishnan]

Please find the attached app

Rgds,

Nimesh

[bhuprakash]

Hi Nimesh,

All the counts coming under Zero while result should come like above.

[lnimeshkrishnan]

Like this?

[bhuprakash]

yes..yes. Like this.

This is correct. And also please add one more thing in this.

In this Dashboard Counts will never come under "0" because ActivityDetails_master file having data only for employees who have done activity so this will not give counts who have not done activity.

So can you please add a lookup ( In Excel language ) with "Daily Activity Count" data in which you find the employee who are not in ActivityDetails_master file and show them in Dashboard.

[lnimeshkrishnan]

Could you please post an image for 1 employee as what you're looking. It seems like all emp_id is available in both data sets.

please find the attached app for your 1st issue.

Rgds,

Nimesh

[bhuprakash]

Hi Nimesh,

I have attached the employee details who have not done any activity in Sep month.

[lnimeshkrishnan]

so, your expected result is 3 isn't it?.. Where do you want to show this in the dashboard, because i when i filter for those employees, the count is 3 under chart SBU wise Average Days.

let me know if i'm missing something.

Rgds,

Nimesh