11 Replies Latest reply on Jul 27, 2018 6:13 AM by Zhouyi Zhang

# WoW KPI change%: how to remove prev. week change %?

Hi,

I am trying to find out the WoW KPI difference between last 2 weeks through colour coding and trend arrows.
The % difference was found out using Quick table calculation- percentage difference.

However I face the below issues.

1. Unable to hide previous week KPI difference(Week 29 in the below snapshot). I require the % difference between Week30 and 29 only.

2. % diff for city: Chicago appears to be wrong as 631% instead of correct figure: 628%

Seems like i have followed a wrong approach. Requesting help to sort this out. Thanks.
Superstore sample workbook attached herewith (V 2018.1.2)

Regards

Abraham

• ###### 1. Re: WoW KPI change%: how to remove prev. week change %?

Hi Abraham,

% is correct you are not consider decimal values if you taken the same in to consideration your value would be 631% only

Hope this helps if you want the output as 628% then first round the decimals to 0 places and then apply the % difference calculation manually instead of using table calculation like mimic the table cal with your own Calc

Hope this helps kindly mark this answer as  correct and helpful so that it will be helpful to others

BR,

NB

1 of 1 people found this helpful
• ###### 2. Re: WoW KPI change%: how to remove prev. week change %?

Hi Abraham,

For part one of the question, there is no way to hide one column without hiding the relative column in the other measure. The best way to approach it is likely to create 3 calculated fields and use those, one for previous week, one for last week and one for week on week.

You've got filters on the sheet for relative dates, are these supposed to be variable, as the calculations would change depending on that.

• ###### 3. Re: WoW KPI change%: how to remove prev. week change %?

Hi, Abraham

Please find my solution attached as well as below screenshot for reference.

Please let me know if you have question.

ZZ

• ###### 4. Re: WoW KPI change%: how to remove prev. week change %?

Hi Zhouyi,

Its works for 2 weeks what happen if three weeks is selected then min will give the week 28 and max will week 30 right ?

what would be the calculation for 3 weeks or i can say N Weeks?

BR,

NB

• ###### 5. Re: WoW KPI change%: how to remove prev. week change %?

Hi Zhouyi,
Thanks for this solution.

I'm not able to figure out the logic on my own.

It would be a great help, if you can explain the approach followed.

Thanks

Abraham

• ###### 6. Re: WoW KPI change%: how to remove prev. week change %?

Hi Abraham,

Check the below steps to know detailed solution approach by Zhouyi Zhang

you selected the filter last 2 weeks which is week 29 and week 30

the below calculation will give the value like difference % table caluclation

Deciphering the below consider the sample date set for one city

1) (ZN({ FIXED [City]:SUM(IF DATETRUNC('week',[Order Date]) = {FIXED :MAX(DATETRUNC('week',[Order Date]))} THEN [Sales] END)}) =  80+90+100+110+120+130+140 = 770

2) ZN({ FIXED [City]:SUM(IF DATETRUNC('week',[Order Date]) = {FIXED :MIN(DATETRUNC('week',[Order Date]))} THEN [Sales] END)}) = 10+20+30+40+50+60+70=280

overall calculation1

(ZN({ FIXED [City]:SUM(IF DATETRUNC('week',[Order Date]) = {FIXED :MAX(DATETRUNC('week',[Order Date]))} THEN [Sales] END)}) = 770

-

ZN({ FIXED [City]:SUM(IF DATETRUNC('week',[Order Date]) = {FIXED :MIN(DATETRUNC('week',[Order Date]))} THEN [Sales] END)}))=280

/

ZN({ FIXED [City]:SUM(IF DATETRUNC('week',[Order Date]) = {FIXED :MIN(DATETRUNC('week',[Order Date]))} THEN [Sales] END)})=280

= (770-280)/280

Second calc will give

I create size() and put it to text

for grandtotal size will give 1 above calculation telling that

if size=1 show me % else show me sum(sales)

this way it  is getting achieved hope this helps

Kindly mark Zhouyi Zhang as Correct and mark this answer as helpful so that it will be helpful to others

BR,

NB

1 of 1 people found this helpful
• ###### 7. Re: WoW KPI change%: how to remove prev. week change %?

Thanks for the detailed explanation, Naveen

• ###### 8. Re: WoW KPI change%: how to remove prev. week change %?

Hi Zhouyi,

How to edit the output column label which is currently shown as 'Grand Total' ?

Need to label it as WoW%.

Thanks

Abraham

• ###### 9. Re: WoW KPI change%: how to remove prev. week change %?

Hi, Abraham

It is under the format, please see my screenshot below

Hope this make sense

ZZ

1 of 1 people found this helpful
• ###### 10. Re: WoW KPI change%: how to remove prev. week change %?

Thanks Zhouyi..

• ###### 11. Re: WoW KPI change%: how to remove prev. week change %?

you are welcome glad to help out

ZZ