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.

[bhuprakash]

Also welcoming your suggestion if it can be more beautifying.

[Anil_Babu_Samineni]

Let's talk first one. What is the logic behind to get Below 3 and Above 3 ?? How come these values are coming in UI?

[martinpohl]

as I can see all average_reporting values are lower than 3, so your syntax

if( count(DISTINCT Activity_Date)/25 < 0, 'Below 3', '0')

is true to be set as 0

Or is your syntax wrong, it should be > 0 ??

Regards

[bhuprakash]

I want to get

count of employee who did not do any activity  - 0

count of employee who did  below 3 activity  - below 3

count of employee who did 3 activity  - 3

count of employee who did more than 3 activity - more than 3

I am calculating this data from Activity Report Master sheet as this data having date wise employee details who did activities. But this data will not give count of employee who did Zero(0) activity.

For this we have another data Activity counts in which all employee details available. So how to do lookup employee who have not done activity.

[bhuprakash]

Hi Martin,

Counts which i have mentioned in my above conversation are calculated in excel. And I want this data in my report but  it is not coming.

[bhuprakash]

Dear All,

waiting for your revert.

[Anil_Babu_Samineni]

Can you guide me exactly what and where we need to look?

[bhuprakash]

Actually when i made it manually then counts are coming as i mentioned in my first conversation.

But if you will see in the qlik dashboard then all counts are coming under "Below 3"  in Average Reporting per Day.

So there is some issue in below load statement -

LOAD emp_id,count(DISTINCT Activity_Date) as User_Days,

   count(DISTINCT Activity_Date)/25 as average_reporting, 

     if( count(DISTINCT Activity_Date)/25 > 3, 'More than 3',

      if( count(DISTINCT Activity_Date)/25 = 3, '3',

      if( count(DISTINCT Activity_Date)/25 > 0, 'Below 3', '0'))) as average_reporting_days

RESIDENT activity_master Group By emp_id;

[Anil_Babu_Samineni]

Can you highlight which report we are talking, Please?