How to calculate percentage and percent difference?

[wanda619]

Hi community,

I am stuck on a problem where i have to calculate percentage and Percent Difference. 

I have 3 columns, for example -- 

Name |  Errorcode |  Result

abc     |   324   |   5

abc     |    999 |   1

abc     |  Total |    6

I want the output to look like this -- 

Name |  Errorcode |  Result | Percent of Total |  Percent Difference ( week over week)

abc     |   324   |   5 |     83.33 | 25

abc     |    999 |   1 | 16.67 |  100

abc     |  Total |    6 | 100 |  100 

for Percent Difference (week over week) should look at the errors for that Name from the prior week and understanding the percent difference to this week.  Example, if there were 3 1027 errorcodes last week and 6 1027 errors this week the percent difference would be 100%. 

[wanda619]

@martinpu I am facing issues in getting previous week results, any other alternative than this, My code - 

| bucket _time span=1w
| lookup table_1 LicenseKey OUTPUT CustomerName
| eval CustomerName=coalesce(CustomerName,LicenseKey)
| stats count as Result by CustomerName, ErrorCode
| eventstats sum(Result) as Total by CustomerName
| eval PercentOfTotal = round((Result/Total)*100,3)
| sort - _time
| streamstats current=f latest(Result) as Result_Prev by CustomerName
| eval PercentDifference = round(((Result/Result_Prev)-1)*100,2)
| fillnull value="0"
| append
[ search index=abc= xyz:123 ErrorCode!=0
| `DedupDHI`
| lookup Table_1 LicenseKey OUTPUT CustomerName
| eval CustomerName=coalesce(CustomerName,LicenseKey)
| stats count as Result by CustomerName
| eval ErrorCode="Total", PercentOfTotal=100]
| fillnull value="0"
| lookup Table_2 ErrorCode OUTPUT Description
| lookup Table_1 LicenseKey OUTPUT CustomerName
| eval CustomerName=coalesce(CustomerName,LicenseKey)
| eval Error=if(ErrorCode!="Total", ErrorCode+" ("+coalesce(Description,"Description Missing - Update Table_2")+")", ErrorCode)
| rename Result_Prev as "Previous Week Results", PercentDifference as " Percent Difference", PercentOfTotal as "Percent of Total"
| fields CustomerName, Error, Result,"Previous Week Results", " Percent Difference" , "Percent of Total"
| sort CustomerName, Error, PercentDifference

Output - 

CustomerName	Error	Result	Previous Week Results	Percent Difference	Percent of Total
AIG Private Client Group	1002 (abc)	4	0	0	3.252
AIG Private Client Group	1003 (cxz)	2	4	-50	1.626
AIG Private Client Group	1013 (Invalid Format)	12	4	200	9.756
AIG Private Client Group	1023 (Invalid Name)	3	4	-25	2.439
AIG Private Client Group	1027 (Invalid )	102	4	2450	82.927
AIG Private Client Group	Total	123	0	0	100
AIICO	1023 (Invalid Name)	8	0	0	38.095
AIICO	1201	1	8	-87.5	4.762
AIICO	1305	12	8	50	57.143
AIICO	Total	21	0	0	100
Acceptance	1023 (Invalid Name)	3	0	0	27.273
Acceptance	1027	8	3	166.67	72.727
Acceptance	Total	11	0	0	100

The Previous Week Results column is appending 4, if noticed which is wrong. any suggestions to solve this
? 

[wanda619]

@martinpu thank you! I have another question, what if the percent difference is neagtive. for example - previous week it was 9 , this week it is 3. how can i show it as -50% ???? 

[martinpu · Accepted solution]

| bucket _time span=1w
| stats c as Result by Name by _time Errorcode name
| eventstats sum(Result) as Result_Total by Errorcode name
| eval 'Percent of Total'=round(100*Result/Result_Total ,2)
| sort - _time
| streamstats current=f latest(Result) as Result_Prev  by Errorcode name
| eval 'Percent Difference'=round(((Result/Result_Prev)-1)*100 ,2))

Maybe like this then ? 

Result	Result_Prev	Percent Difference'
3	2	50
10	5	100
3	9	-66.77
3	3	0

It is a more accurate percent difference calculation 
 

[martinpu]

Something like this?

| bucket _time span=1w
| stats c as Result by Name by _time Errorcode name
| eventstats sum(Result) as Result_Total by Errorcode name
| eval 'Percent of Total'=round(100*Result/Result_Total ,2)
| sort - _time
| streamstats current=f latest(Result) as Result_Prev  by Errorcode name
| eval 'Percent Difference'=round(100*Result/Result_Prev ,2)

And then follow it up with table command to reorder the fields.

I'm not sure if it's going to give you the right results, maybe the sort should be the other way around.