60 Replies Latest reply: Apr 27, 2017 11:17 AM by Wallo Atkinson

# Slow Calculations

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

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

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

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

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

thanks

• ###### Re: Slow Calculations

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

• ###### Re: Slow Calculations

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

Hi Thom,

could you explain what is stored exactly in the fields:

FISPD, FISPD_CNT, FISYR, FISYR_CNT ???

• ###### Re: Slow Calculations

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

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

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

FISPD and FISPD_CNT

• ###### Re: Slow Calculations

you're right, you cant't

• ###### Re: Slow Calculations

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

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

Still very slow and pegging CPU at 100%

• ###### Re: Slow Calculations

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

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

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

• ###### Re: Slow Calculations

and joining this fields into PA0000? multiplies the rows or?

• ###### Re: Slow Calculations

I was able to get everything onto one table using the function Concat.  Here is my script and at the bottom I shared a snapshot of the report.

Temp:
FISYR,
[Billing Date] as  [Start Date],
WEDAT,
WKNO
// autonumber(FISYR&'|'&FISPD) as KEY2
FROM [D:\Qlikview\New Sales\QVD\ZZZWEDAT DaveyTree v1.0.qvd] (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)
[End Date],
[Start Date],
Year((Date(Floor([Start Date])))) as StartYear,
num(Date(Floor([Start Date]))) as NumStartDate,
[Employee Number],
[Employee Number] as PA0000_EmpID,
[Employee Number] as PA0041_EmpID,
[Reason f.Action_MASSG],
[Action Type_MASSN] as [Action Type],
Employment_STAT2,
[Week Ending],
[Reason f.Action_MASSG] & [Action Type_MASSN] as [Term Event]
FROM
[D:\Qlikview\QVD\PA0000.qvd] (
qvd)
Where Employment_STAT2 = '0' or
Employment_STAT2 = '1' or
Employment_STAT2 = '2'
;

Right Join (Temp)
Max([Start Date]) as [Start Date]
Resident Temp
Where Year([Start Date]) >= '\$(vPrevYear)'
Group By [Employee Number]

;
NoConcatenate
PA0000:
if(Match([Action Type],'24','25','26','27','77','78','79','84'),1,0) as InActive
Resident Temp
Where Year([Start Date]) >= '\$(vPrevYear)'
;

DROP Table Temp;

Left join (PA0000)
[Employee Number],
[Employee Number] as EmpNo,
[Profit Center],
Job_STELL
FROM
[D:\Qlikview\QVD\PA0001.qvd]
(
qvd)
where [End Date] = '9999-12-31'
;

Emp_Summ:
FISYR
QTR
FISPD,
WEDAT,
WKNO,
TIMEIND,
Avg_Emp_Cnt
FROM
(
qvd);

CONCATENATE  (PA0000)
RESIDENT Emp_Summ;

DROP TABLE Emp_Summ;

EXIT Script;

Now if you notice my report I have one line which is null values and I need to display that value on every line.....for example for 2017 the number 18 should be on every line so I can calc the % by action.

Thanks

• ###### Re: Slow Calculations

You can use the TOTAL qualifier in your expression to achieve that (use a field list to only apply total on parts of your dimensions, not listed in the field list):

For example

=Sum(TOTAL<FISYR> Avg_Emp_Cnt)

• ###### Re: Slow Calculations

Thanks Stefan,

Worked perfectly.  Now the next question.  I have Quarters, months and weeks.  How can that be handled?  Here is what I see with quarters.....

• ###### Re: Slow Calculations

Maybe just adapt the TOTAL qualifier field list to the fields used in your dimension (the dimension fields you still like to group your data by).

=Sum(TOTAL<QTR> Avg_Emp_Cnt)

If you need to restric the data to specific years and you do this in your expression, apply the same using e.g. set analysis.

BTW. It's easier to help if you describe your charts in terms of dimensions and expressions used (in combination with a screenshot).

• ###### Re: Slow Calculations

I have 2 tables which I have concatenated.  One is a detail table and the other is summary count of the average number of employees by Office, Year, Quarter, Month and Week.  I need to create charts showing reason for turnover (action) based on the above time dims.  The example you furnished me for years works great.  I used this:

Sum(TOTAL{<FISYR = {\$(=\$(vFisYr) - 0)},TIMEIND = {Y}>} Avg_Emp_Cnt)

The attached screen shot is based on Quarters, but shows the total year.  When I change the TIMEIND to "Q" is sums all the Quarters together (50).  I should have 22 employees in Q1, and 28 employees in Q2.

• ###### Re: Slow Calculations

The total qualifier and a set expression are two distinct concepts

Total qualifier with field list, e.g.

TOTAL<QTR>

Set Expression with year field modifier:

{<Year = {2017}>}

The Aggregation Scope

• ###### Re: Slow Calculations

Okay I think I got it (or pretty close).  For some reason I am getting Null values in my actions (terminations).  Here is the chart:  The top part is right 24 and 26.  Don't need the bottom.  Here are the calculations I am using in 2017 and 2016.

Sum(TOTAL<QTR>{<FISYR = {\$(=\$(vFisYr) - 0)},TIMEIND = {Q}>}  Avg_Emp_Cnt)

• ###### Re: Slow Calculations

Maybe just enable 'suppress when value is null' on dimension tab for your action dimension.

• ###### Re: Slow Calculations

Never mind it was me.  Been up way too many hours looking at this.  It's the total line.  I need to suppress my total line in a pivot table.

• ###### Re: Slow Calculations

By adding FISPD and FISYR to PA0000, can't you just do:

=count(distinct {<InActive = {1}>} [Employee Number])

And not go to the EMP_CNTS table at all?

• ###### Re: Slow Calculations

=count(distinct {<InActive = {1}>} [Employee Number])  This tells me how many employees have been terminated and why.  The EMP_CNT table tells me how many active employees I had each year,quarter, month and week. I need both to determine turnover %.

Thanks

• ###### Re: Slow Calculations

I gotcha.

I think the only way you're going to get rid of your Key1=Key2 is to create a link table in the middle of the 3 tables PA0000, EMP_CNTS, and Cost Center...

Doing something like this maybe:

Link Table in QlikView – Learn QlikView

• ###### Re: Slow Calculations

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

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

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

So, to summarize my understanding:

- you have multiple fact tables (some of them aggregated) and to avoid synthetic keys, you renamed identical fields like Profit Center, FY, FQ, FP in one (some) of the tables

- now, you are trying to restore the same associations in a chart expression using multiple IF Conditions and tying these multiple renamed fields, and it is obviously very slow.

If I may suggest... You are always better off to leverage Qlik associative logic and resolve these issues in your data model, rather than in a chart expression. Working with Qlik data associations is a lot faster than trying to tie the same fields using IF conditions.

So, one option is to build a Link Table that would connect the multiple fact tables with their identical Key fields (Profit Center, TY, FQ, FP, ...). Refer to Barry Harmsen's "Data Modeling" session from the Masters Summit for a practical example of how to do it.

For truly large data sets, however, we know that Link Tables don't perform very well (remember my Performance Tuning session at the Masters Summit?). Therefore, we typically recommend concatenating multiple facts into a single Fact Table. Sometimes it requires restoring some missing associations, but it's worth the trouble because of the better performance. Barry's session covers these topics, too.

Once you have one of the two "valid" data models for multiple fact tables (either a Link Table, or a Concatenated Fact), then you won't need to tie multiple renamed fields together, and your remaining conditions will be simple enough for a simple Set Analysis (covered in my session at the Masters Summit).

cheers,

Oleg Troyansky

• ###### Re: Slow Calculations

I took your suggestion and created link tables.  However, this is what I see.  I need the summary totals for each action because that is the total number of employees in each qtr compared to the total for the action.  When I suppress null values I get zeros in the 2017 - 2013 columns. How can I associate the totals with each action?  I have made my linkkey (Profit Center, Fisyr, Qtr, Fispd, Wkno, Wedat).  Thanks

• ###### Re: Slow Calculations

could you post your app ?

• ###### Re: Slow Calculations

Not easily....

• ###### Re: Slow Calculations

ok, what is the formula for KPI ?

• ###### Re: Slow Calculations

2017:

Sum(TOTAL<FISYR,QTR>{<FISYR = {\$(=\$(vFisYr) - 0)},TIMEIND = {Q}>} Avg_Emp_Cnt)

Action:

count({<InActive = {1},FISYR = {\$(vFisYr)}>} DISTINCT([Employee Number]))

%

count({<InActive = {1},FISYR = {\$(vFisYr)}>} DISTINCT([Employee Number])) /
Sum(TOTAL<FISYR,QTR>{<FISYR = {\$(=\$(vFisYr) - 0)},TIMEIND = {Q}>} Avg_Emp_Cnt)

I have attached the qvw

• ###### Re: Slow Calculations

there is no data for 2013 it is why is 0

• ###### Re: Slow Calculations

What did you do?

• ###### Re: Slow Calculations

She added a straight table with a sum by year.  No 2013 when TIMEIND= Y.

• ###### Re: Slow Calculations

I rewrite the expression that couse problems, I did not change the your table to diffeent format  , I add extra chart to see if my calculation is correct

• ###### Re: Slow Calculations

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

Thom

• ###### Re: Slow Calculations

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)

• ###### Re: Slow Calculations

Wallo

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

Thanks

• ###### Re: Slow Calculations

Wallo,

Ignore my previous response.  Forgot to check the box ignore null values on Profit Center.....been working on this way too long.

Thanks

• ###### Re: Slow Calculations

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.

• ###### Re: Slow Calculations

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

• ###### Re: Slow Calculations

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:
FISYR,
[Billing Date],
WEDAT,
WKNO
FROM [D:\Qlikview\New Sales\QVD\ZZZWEDAT DaveyTree v1.0.qvd] (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:
FISYR,
[Billing Date] as Date_ZWKDATE,
WEDAT,
WKNO
FROM [D:\Qlikview\New Sales\QVD\ZZZWEDAT DaveyTree v1.0.qvd] (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)
[Cost Ctr_ZKOSTL] as [Profit Center],
Date_ZWKDATE,
EmpCt_ZEMPCT
FROM
(
qvd)
where [Cost Ctr_ZKOSTL] <> ' ';

NoConcatenate
EMP:
FISYR,
QTR,
FISPD,
MTH,
WKNO,
WEDAT,
[Profit Center],
EmpCt_ZEMPCT
Resident Temp
;
DROP Table Temp;

EMP_QTR:
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:
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:
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:
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:
[Profit Center],
Avg_Emp_Cnt,
TIMEIND
FROM
(
qvd);

EMP_QTR:
QTR,
[Profit Center],
Avg_Emp_Cnt,
TIMEIND
FROM
(
qvd);

CONCATENATE  (EMP_COUNT)
RESIDENT EMP_QTR;

Drop Table EMP_QTR;

EMP_MTH:
FISPD,
[Profit Center],
Avg_Emp_Cnt,
TIMEIND
FROM
(
qvd);

CONCATENATE  (EMP_COUNT)
RESIDENT EMP_MTH;

Drop Table EMP_MTH;

EMP_WEDAT:
WEDAT,
WKNO,
[Profit Center],
Avg_Emp_Cnt,
TIMEIND
FROM
(
qvd);

CONCATENATE  (EMP_COUNT)
RESIDENT EMP_WEDAT;

Drop Table EMP_WEDAT;

NoConcatenate
EMP_CNTS:
Resident EMP_COUNT;

STORE * FROM [EMP_CNTS] INTO QVD/EMP_CNTS.QVD;

DROP TABLE [EMP_CNTS];

• ###### Re: Slow Calculations

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

• ###### Re: Slow Calculations

As a side note, when you are concatenating numbers like that for a key, you probably want to use a delimiter type character to separate the fields within the key.  I usually use a pipe.  For example.

=Field1 & '|' & Field2 & '|' & Field3

This should eliminate the chance of incorrect key duplication.

• ###### Re: Slow Calculations

I will say to use auto number function

• ###### Re: Slow Calculations

I would think you would still need a character or autonumber could generate duplicate autonumber value?

So you would need something like this:

=autonumber(Field1 & '|' & Field2 & '|' & Field3)

• ###### Re: Slow Calculations

no it cannot be duplicate but this ID will work in one app

its enough

=autonumber(Field1  & Field2 & Field3)

• ###### Re: Slow Calculations

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)

• ###### Re: Slow Calculations

You have to keep the TIMEIND at Q because I what Quarterly measures.  There are separate measures based on Y,Q,M,W TIMEIND.....

• ###### Re: Slow Calculations

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

• ###### Re: Slow Calculations

Glad to hear you're making progress.  See attached.  I added a different pivot table that seems to be more accurate and simpler to me.

I can't help but feel like there are better ways to do what you're attempting by making data model changes.

To get rid of the null Action you would need to add Action as part of your link key.

I would probably do an inner join between your summary data and all distinct action so that I could then add it to the link key,  Then your null actions would be gone.  And also add a manual Action for the summary data of 'TOTAL' so I could use set analysis wherever I needed to get the summary total without duplication.

• ###### Re: Slow Calculations

try

count(distinct({<FISPD = {FISPD_CNT} ,InActive = {1} , FISYR ={FISYR_CNT}>}[Employee Number]))

Could you share your app ? I would like to see the model.

Anna