Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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])))
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.
FISYR and FISYR_CNT are in 2 separate tables....how can I used autonumber, same with
FISPD and FISPD_CNT
you're right, you cant't
--> 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])))
Still very slow and pegging CPU at 100%
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
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
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
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
FISPD and FISPD_CNT are in 2 separate tables.....