Announcements
cancel
Showing results for
Did you mean:
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
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)

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.

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)

Creator III

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

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:
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:
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)
[Cost Ctr_ZKOSTL] as [Profit Center],
Date_ZWKDATE,
EmpCt_ZEMPCT
FROM

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

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

EMP_QTR:
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:
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:
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:
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:
[Profit Center],
Avg_Emp_Cnt,
TIMEIND
FROM

(
qvd);

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

(
qvd);

CONCATENATE  (EMP_COUNT)
RESIDENT EMP_QTR;

Drop Table EMP_QTR;

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

(
qvd);

CONCATENATE  (EMP_COUNT)
RESIDENT EMP_MTH;

Drop Table EMP_MTH;

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

(
qvd);

CONCATENATE  (EMP_COUNT)
RESIDENT EMP_WEDAT;

Drop Table EMP_WEDAT;

NoConcatenate
EMP_CNTS:
Resident EMP_COUNT;

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

DROP TABLE [EMP_CNTS];

Specialist II
Author

Wallo

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

Thanks

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

Specialist II
Author

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

Specialist II
Author

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

Community Browser