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

[swuehl]

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

[annafuksa1]

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

[tmumaw]

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)

[swuehl]

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

Or return the expression result conditionally based on Action:

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

[tmumaw]

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.

[tmumaw]

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

[annafuksa1]

could you post your app ?

[tmumaw]

Not easily....

[annafuksa1]

ok, what is the formula for KPI ?

[tmumaw]

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