Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
tmumaw
Specialist II
Specialist II

Slow Calculations

Good Morning everyone,

I am having an issue with the amount of time it's taking to do a calculation.  When I select a singles sales office response is fine, however when I select a company code the CPU pegs.....Here is my count statement.

Thanks

count(distinct(if(FISPD = FISPD_CNT and InActive = '1' and FISYR = FISYR_CNT,[Employee Number])))

60 Replies
annafuksa1
Creator III
Creator III

for this app it can be use

AutoNumber(   [Profit Center] & FISYR & QTR & FISPD & WKNO & WEDAT )as %LinkKey  

it can speed up your app (if there is a lot of data change can be significant)

tmumaw
Specialist II
Specialist II
Author

Anna,

Check this one out.  This is what one of the reports needs to look like without the action being blank.  The blank action is coming from the summary table.  The summary table is made up of employee totals by profit center, year (Y timeind), quarter (Q timeind), period (M timeind), W (W timeind).  I need to match the summary table  EMP_Sum (summary totals)  with the details from the Employee Detail.  The detail table carries why the employee was terminated. The summary table does not have the action on it.

tmumaw
Specialist II
Specialist II
Author

I am thinking I might have to figure out a way of merging the 2 tables.

Thom

Anonymous
Not applicable

Probably - almost certainly not the most efficient answer but seems to work when I try it.

Multiply ALL your expressions by count(Action).

This will cause the rows with the blank action to calculate out to 0.  As long as your Presentation tab has all expressions set to Suppress Zero Values, it should solve your null Action problem.

Depending on how you want filtering to work you may also want to add a 1 to your set analysis,

Example: 

so this with the 1 added.

=Sum(Total<FISYR,QTR>{1<FISYR = {$(=$(vFisYr))},TIMEIND = {Q}>} Avg_Emp_Cnt)*count(Action)

or

Sum(Total<FISYR,QTR>{<FISYR = {$(=$(vFisYr))},TIMEIND = {Q}>} Avg_Emp_Cnt)*count(Action)

without the 1 added

annafuksa1
Creator III
Creator III

I am sorry i did not understand you. Can you explain ?

tmumaw
Specialist II
Specialist II
Author

There are 2 tables a summary table which has counts of employees by profit center based on a time-ind.  The time-ind can have values of Y for year, Q for quarter, M for month and W for week.  Take a look at this script it might make more since.

CurrentDate:
LOAD FISPD,
FISYR,
[Billing Date],
WEDAT,
WKNO
FROM (qvd)
where [Billing Date] = '$(vToday)';

LET vFisYr = peek('FISYR', 0, 'CurrentDate');
LET vFisPd = peek('FISPD', 0, 'CurrentDate');
LET vWkNo  = peek('WKNO', 0, 'CurrentDate');

DROP TABLE CurrentDate;

Temp:
LOAD FISPD,
FISYR,
[Billing Date] as Date_ZWKDATE,
WEDAT,
WKNO
FROM (qvd)
where FISYR >= '$(vPYear)' and FISYR < '$(vYear)';

Left Join Temp:
LOAD * INLINE [FISPD, QTR, MTH
001, 1, Jan,
002, 1, Feb,
003, 1, Mar,
004, 2, Apr,
005, 2, May,
006, 2, Jun,
007, 3, Jul,
008, 3, Aug,
009, 3, Sep,
010, 4, Oct,
011, 4, Nov,
012, 4, Dec
]
;

Left Join (Temp)
LOAD //CoCode_ZBUKRS,
   [Cost Ctr_ZKOSTL] as [Profit Center],
Date_ZWKDATE,
EmpCt_ZEMPCT
FROM

(
qvd)
where [Cost Ctr_ZKOSTL] <> ' ';

NoConcatenate
EMP:
Load
FISYR,
QTR,
FISPD,
MTH,
WKNO,
WEDAT,
[Profit Center],
EmpCt_ZEMPCT
Resident Temp
;
DROP Table Temp;


EMP_QTR:
Load
FISYR,
QTR,
[Profit Center],
avg(EmpCt_ZEMPCT) as Avg_Emp_Cnt,
'Q' 
as TIMEIND

Resident EMP
Group By [Profit Center], FISYR, QTR //, FISPD //, MTH //, WKNO,[WEDAT]
;

STORE * FROM [EMP_QTR] INTO QVD/EMP_QTR.QVD;

DROP TABLE [EMP_QTR];

EMP_MTH:
Load
FISYR,
FISPD,
[Profit Center],
avg(EmpCt_ZEMPCT) as Avg_Emp_Cnt,
'M' 
as TIMEIND

Resident EMP
Group By [Profit Center], FISYR, FISPD //QTR //, //, MTH //, WKNO,[WEDAT]
;

STORE * FROM [EMP_MTH] INTO QVD/EMP_MTH.QVD;

DROP TABLE [EMP_MTH];

EMP_YEAR:
Load
FISYR,
[Profit Center],
avg(EmpCt_ZEMPCT) as Avg_Emp_Cnt,
'Y' 
as TIMEIND

Resident EMP
Group By [Profit Center], FISYR //, FISPD //QTR //, //, MTH //, WKNO,[WEDAT]
;

STORE * FROM [EMP_YEAR] INTO QVD/EMP_YEAR.QVD;

DROP TABLE [EMP_YEAR];

EMP_WEDAT:
Load
FISYR,
WEDAT,
WKNO,
[Profit Center],
avg(EmpCt_ZEMPCT) as Avg_Emp_Cnt,
'W' 
as TIMEIND

Resident EMP
Group By [Profit Center], FISYR, WEDAT, WKNO //, FISPD //QTR //, //, MTH //, WKNO,[WEDAT]
;

STORE * FROM [EMP_WEDAT] INTO QVD/EMP_WEDAT.QVD;

DROP TABLE [EMP_WEDAT];

DROP Table EMP;

EMP_COUNT:
LOAD FISYR,
[Profit Center],
Avg_Emp_Cnt,
TIMEIND
FROM

(
qvd);

EMP_QTR:
LOAD FISYR,
QTR,
[Profit Center],
Avg_Emp_Cnt,
TIMEIND
FROM

(
qvd);

CONCATENATE  (EMP_COUNT)
LOAD *
RESIDENT EMP_QTR;

Drop Table EMP_QTR;

EMP_MTH:
LOAD FISYR,
FISPD,
[Profit Center],
Avg_Emp_Cnt,
TIMEIND
FROM

(
qvd);

CONCATENATE  (EMP_COUNT)
LOAD *
RESIDENT EMP_MTH;

Drop Table EMP_MTH;

EMP_WEDAT:
LOAD FISYR,
WEDAT,
WKNO,
[Profit Center],
Avg_Emp_Cnt,
TIMEIND
FROM

(
qvd);

CONCATENATE  (EMP_COUNT)
LOAD *
RESIDENT EMP_WEDAT;

Drop Table EMP_WEDAT;

NoConcatenate
EMP_CNTS:
Load *
Resident EMP_COUNT;

STORE * FROM [EMP_CNTS] INTO QVD/EMP_CNTS.QVD;

DROP TABLE [EMP_CNTS];

tmumaw
Specialist II
Specialist II
Author

Wallo

Check this one out.  I added your logic with 2 profit centers.  Am I missing something?

Thanks

tmumaw
Specialist II
Specialist II
Author

Wallo,

Ignore my previous response.  Forgot to check the box ignore null values on Profit Center.....been working on this way too long.

Thanks

tmumaw
Specialist II
Specialist II
Author

Thanks for all your help....getting closer.

tmumaw
Specialist II
Specialist II
Author

Thanks for all your help....getting closer.