topic Re: Issue in Pivot table in QlikView

Issue in Pivot table

bhuprakash — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Issue in Pivot table

bhuprakash — Sat, 30 Dec 2017 16:24:16 GMT

Also welcoming your suggestion if it can be more beautifying.

Re: Issue in Pivot table

Anil_Babu_Samineni — Sat, 30 Dec 2017 17:14:14 GMT

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?

Re: Issue in Pivot table

martinpohl — Sat, 30 Dec 2017 17:23:56 GMT

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

Re: Issue in Pivot table

bhuprakash — Sun, 31 Dec 2017 14:56:24 GMT

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.

Re: Issue in Pivot table

bhuprakash — Sun, 31 Dec 2017 15:17:25 GMT

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.

Re: Issue in Pivot table

bhuprakash — Sat, 06 Jan 2018 15:03:02 GMT

Dear All,

waiting for your revert.

Re: Issue in Pivot table

Anil_Babu_Samineni — Sat, 06 Jan 2018 15:17:38 GMT

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

Re: Issue in Pivot table

bhuprakash — Sat, 06 Jan 2018 15:30:00 GMT

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;

Re: Issue in Pivot table

Anil_Babu_Samineni — Sat, 06 Jan 2018 15:52:23 GMT

Can you highlight which report we are talking, Please?

Re: Issue in Pivot table

devarasu07 — Sat, 06 Jan 2018 17:19:53 GMT

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

Re: Issue in Pivot table

bhuprakash — Sat, 06 Jan 2018 17:34:30 GMT

Hi Deva,

It is giving same result like before.

Re: Issue in Pivot table

devarasu07 — Sat, 06 Jan 2018 17:58:50 GMT

hi,

can you share your source file "ActivityDetails_master.xlsx"

Uploading a Sample

Re: Issue in Pivot table

lnimeshkrishnan — Sun, 07 Jan 2018 08:10:33 GMT

Please find the attached app

Rgds,

Nimesh

Re: Issue in Pivot table

bhuprakash — Mon, 08 Jan 2018 10:04:14 GMT

Hi Nimesh,

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

Re: Issue in Pivot table

lnimeshkrishnan — Mon, 08 Jan 2018 10:06:45 GMT

Like this?

Re: Issue in Pivot table

bhuprakash — Mon, 08 Jan 2018 10:12:13 GMT

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.

Re: Issue in Pivot table

lnimeshkrishnan — Mon, 08 Jan 2018 10:34:19 GMT

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

Re: Issue in Pivot table

bhuprakash — Mon, 08 Jan 2018 12:42:39 GMT

Hi Nimesh,

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

Re: Issue in Pivot table

lnimeshkrishnan — Mon, 08 Jan 2018 13:17:33 GMT

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