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

[sunny_talwar]

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

[tmumaw]

Sort of hard to create a flag when the values are in two different tables....The FISYR_CNT and FISPD are in the EMP_CNTS table, FISYR and FISPD are in FiscalCalendar and InAvtive is in PA0000.

[sunny_talwar]

In that case you can either create a new table in the script where you can bring the necessary fields into single table to perform your test or I guess you will have to live with the slowness....

or you can try the below which may or may not help

Count(DISTINCT {<[Employee Number] = {"=FISPD = FISPD_CNT and FISYR =FISYR_CNT"}, InActive = {1}>} [Employee Number])

[tmumaw]

Count(DISTINCT {<[Employee Number] = {"=FISPD = FISPD_CNT and FISYR =FISYR_CNT"}, InActive = {1}>} [Employee Number])  brought back zeros.....

thanks

[sunny_talwar]

Ya I feared that... don't think there is another way

[Anonymous]

I think:

<[Employee Number] = {"=FISPD = FISPD_CNT and FISYR =FISYR_CNT"}...

ist the same as

<[Employee Number] = {"=only(FISPD) = only(FISPD_CNT) and only(FISYR) =only(FISYR_CNT)"}...

in the context of each dimension [Employee Number]

I think this won't work.

Or am I wrong Sunny?

[Anonymous]

Hi Thom,

could you explain what is stored exactly in the fields:

FISPD, FISPD_CNT, FISYR, FISYR_CNT ???

[tmumaw]

FISPD is (001 - 012) months from table (FiscalCalendar)

FISPD_CNT is (001 - 012) months from summary table (EMP_CNTS)

FISYR is (2013 - 2017) years from table (FiscalCalendar)

FISYR_CNT is (2013 - 2017) years from table (EMP_CNTS)

[Anonymous]

please try:

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

and

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

and change you expression the following way:

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