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])))
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}>}
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
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)
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) )
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.
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
could you post your app ?
Not easily....
ok, what is the formula for KPI ?
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