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

[vinieme12]

Your fields "FISYR" and "FISYR_CNT" from the Fiscalcalendar are based on the "StartDate" from PA0000,

You can easily recreate them in PA0000 for creating the FLAG field.

[tmumaw]

FISYR and FISYR_CNT are in 2 separate tables....how can I used autonumber, same with

FISPD and FISPD_CNT

[Anonymous]

you're right, you cant't

[Anonymous]

--> i just mixed it up... perhaps it is a bit faster...

please try:

autonumber(FISYR_CNT&'|'&FISPD_CNT) as KEY1

and

autonumber(FISYR&'|'&FISPD) as KEY2

and change you expression the following way:

count({<InActive = {1}>} distinct(if(KEY1= KEY2,[Employee Number])))

[tmumaw]

Still very slow and pegging CPU at 100%

[Oleg_Troyansky]

Hi Thom,

can you please describe in plain English what business question are you trying to answer, and what is your data structure that you use to answer this question? I'm pretty sure there must be a better way...

cheers,

Oleg Troyansky

[tmumaw]

There's a business requirement to track total employee counts and turnover percentages for the past 5 years (Year, Quarter, Month, Week)  by action (termination, layoff, leave of absence,Etc.) and reason for termination (lack of work, attend school, illness / injury, Etc.).  I have created a summary file EMP_CNTS which contains the total number of employees by year, quarter, month and week.  I need to get the actions and reasons from PA0000 which carries the date of termination.  Hope this helps.  Thanks

[tmumaw]

I have modified my script to include FISYR, QTR, FISPD, WEDAT and WKNO in PA0000.  I added KEY1 and KEY2, but still very slow.

thanks

[Anonymous]

a) So did you try the approach of Sunny with the flag?

Create a flag in the script

If(FISPD = FISPD_CNT and InActive = '1' and FISYR =FISYR_CNT, 1, 0) as Flag

and then this

Count(DISTINCT {<Flag = {1}>} [Employee Number])

b) How many rows are in PA0000?

c) Which kind of chart do you use?

d) you can remove KEY1 and KEY2...

Please provide as much information as possible

[tmumaw]

FISPD and FISPD_CNT are in 2 separate tables.....