How to calculate previous week results?

[wanda619]

Hi community,

I have to calculate previous week result, based on that, I calculate Percent difference with this weeks results. I have following code, but not able to get previous week result right.

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
? 

[bowesmana]

I assume there is more search prior to the bucket command, however, this first part of your search is wrong, you are doing

| 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

however, you are doing stats count... but do not include _time as a split by field, so you no longer have the _time field available when you do the 'sort - _time' and the stats are not split by week. 

When you use bucket _time span=1w, you then need to do stats ... by _time, A, B...

streamstats also needs global=f and window=1 to do what you are trying to do

[wanda619]

@bowesmana is there any other alternative than using bucket_time span? to acheive the result

[bowesmana]

bucket is perfectly valid time span but it will depend on where your data is coming from for each week. In your example you are making one search then appending another. 

If your basic search is the same for both weeks, then I would search something like this

base_search
| bin _time span=1w
| lookup table_1 LicenseKey OUTPUT CustomerName
| eval CustomerName=coalesce(CustomerName,LicenseKey)
| stats count as Result by _time CustomerName, ErrorCode
| streamstats global=f window=1 current=f latest(Result) as prev by CustomerName, ErrorCode
| where isnotnull(prev)
| eval PercentDifference = round(((Result/prev)-1)*100,2)

this basic technique will give you the percentage increase of the previous week.

The where clause removes the rows where there is no previous value, i.e. the previous figures, which are carried forward to the 'next' week. Sorting is not necessary as the stats by _time will order them correctly.

Hope this helps.

[wanda619]

@bowesmana Correct the basic search is same for both. 

The where clause I believe works but it messes up with the output for Error, as It does not allows me to show all the other error code output. for example - 

CustomerName	Error	Result	Previous Week Results	Percent Difference	Percent of Total	_time
Client1	1003 (Invalid Birth Year)	1	2	-50	0.885	2022-08-17T00:00:00.000-0500
Client1	1023 (Invalid Name)	1	2	-50	0.885	2022-08-17T00:00:00.000-0500
Client1	1027 (Invalid UnderwritingState)	2	83	-97.59	1.77	2022-08-17T00:00:00.000-0500
Client1	Total	113	0	0	100	1969-12-31T18:00:00.000-0500
Client2	1023 (Invalid Name)	1	11	-90.91	4.348	2022-08-17T00:00:00.000-0500
Client2	Total	23	0	0	100	1969-12-31T18:00:00.000-0500
Client3	1027 (Invalid UnderwritingState)	1	10	-90	7.143	2022-08-17T00:00:00.000-0500
Client3	Total	14	0	0	100	1969-12-31T18:00:00.000-0500
Client4	1013 (Invalid Drivers License Format)	1	8	-87.5	3.846	2022-08-17T00:00:00.000-0500
Client4	Total	26	0	0	100	1969-12-31T18:00:00.000-0500
Client5	Total	2	0	0	100	1969-12-31T18:00:00.000-0500
Client6	Total	8	0	0	100	1969-12-31T18:00:00.000-0500
Client7	Total	1	0	0	100	1969-12-31T18:00:00.000-0500
Client8	1013 (Invalid Drivers License Format)	2	3	-33.33	1.681	2022-08-17T00:00:00.000-0500
Client8	1023 (Invalid Name)	1	8	-87.5	0.84	2022-08-17T00:00:00.000-0500
Client8	1027 (Invalid UnderwritingState)	4	95	-95.79	3.361	2022-08-17T00:00:00.000-0500
Client8	Total	119	0	0	100	1969-12-31T18:00:00.000-0500
Client9	1013 (Invalid Drivers License Format)	15	165	-90.91	7.772	2022-08-17T00:00:00.000-0500
Client9	Total	193	0	0	100	1969-12-31T18:00:00.000-0500
Client10	Total	6	0	0	100	1969-12-31T18:00:00.000-0500
Client11	Total	1197	0	0	100	1969-12-31T18:00:00.000-0500

The Client 5,6,7 as well as 10,11 just shows up with total.

[wanda619]

@bowesmana or previous week result