topic Re: Slow Calculations in QlikView

Slow Calculations

tmumaw — Wed, 19 Apr 2017 12:11:12 GMT

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

Re: Slow Calculations

sunny_talwar — Wed, 19 Apr 2017 12:12:58 GMT

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

Re: Slow Calculations

tmumaw — Wed, 19 Apr 2017 12:21:00 GMT

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.

Re: Slow Calculations

sunny_talwar — Wed, 19 Apr 2017 12:32:56 GMT

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

Re: Slow Calculations

tmumaw — Wed, 19 Apr 2017 12:39:50 GMT

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

thanks

Re: Slow Calculations

sunny_talwar — Wed, 19 Apr 2017 12:50:15 GMT

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

Re: Slow Calculations

Anonymous — Wed, 19 Apr 2017 13:03:18 GMT

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?

Re: Slow Calculations

Anonymous — Wed, 19 Apr 2017 13:09:32 GMT

Hi Thom,

could you explain what is stored exactly in the fields:

FISPD, FISPD_CNT, FISYR, FISYR_CNT ???

Re: Slow Calculations

tmumaw — Wed, 19 Apr 2017 13:22:58 GMT

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)

Re: Slow Calculations

Anonymous — Wed, 19 Apr 2017 13:36:13 GMT

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

Re: Slow Calculations

vinieme12 — Wed, 19 Apr 2017 13:42:50 GMT

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.

Re: Slow Calculations

tmumaw — Wed, 19 Apr 2017 13:52:04 GMT

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

FISPD and FISPD_CNT

Re: Slow Calculations

Anonymous — Wed, 19 Apr 2017 15:39:18 GMT

you're right, you cant't

Re: Slow Calculations

Anonymous — Wed, 19 Apr 2017 15:47:54 GMT

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

Re: Slow Calculations

tmumaw — Wed, 19 Apr 2017 18:15:50 GMT

Still very slow and pegging CPU at 100%

Re: Slow Calculations

Oleg_Troyansky — Wed, 19 Apr 2017 20:14:06 GMT

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

Re: Slow Calculations

tmumaw — Thu, 20 Apr 2017 10:52:38 GMT

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

Re: Slow Calculations

tmumaw — Thu, 20 Apr 2017 12:17:57 GMT

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

Re: Slow Calculations

Anonymous — Thu, 20 Apr 2017 15:15:03 GMT

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

Re: Slow Calculations

tmumaw — Thu, 20 Apr 2017 15:19:39 GMT

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