Slow Calculations

[tmumaw]

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])))

[annafuksa1]

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]

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]

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

Thom

[Anonymous]

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]

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

[tmumaw]

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]

Wallo

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

Thanks

[tmumaw]

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]

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

[tmumaw]

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