% of total

[schou87]

hi I have a table as shown below. I want to get the % of total for each status for previous 6 days. How do i write a query to get the same.

DATE	Status_1	Status_2	Status_3
2021-05-19	14	33	123
2021-05-18	45	12	456
2021-05-17	4	6	213
2021-05-16	5	8	564
2021-05-15	4	9	987
2021-05-14	4	0	543

[ITWhisperer]

| eventstats sum(Status_*) as Total_*
| foreach Status_*
    [| eval Percentage_<<MATCHSTR>>=100*<<FIELD>>/Total_<<MATCHSTR>>]

[schou87]

Hi,

I am getting an error  : Unencoded <

Also, can we please consider the below:

Status_1: A

Status_2: B

Status_3: C

I don't want to use matchstring as there is no wildcard match in my example. That was just an example.

[ITWhisperer]

| makeresults 
| eval _raw="DATE	A	B	C
2021-05-19	14	33	123
2021-05-18	45	12	456
2021-05-17	4	6	213
2021-05-16	5	8	564
2021-05-15	4	9	987
2021-05-14	4	0	543"
| multikv forceheader=1
| fields - _raw linecount
| eval _time=DATE
| fields - DATE
| eventstats sum(*) as Total_*
| foreach *
    [| eval Percentage_<<FIELD>>=100*<<FIELD>>/Total_<<FIELD>>]
| rename _time as DATE

[schou87]

Hi, your query gives me % total for each field individually on daily basis but can you tell me how to do a % of total for previous 6 days together and not calculate day on day basis. So I just want to return 3 rows for % total of my 3 fields.

Like % A=  A/A+B+C *100 ( for all 6 days together and not by individual dates)

[ITWhisperer]

| makeresults 
| eval _raw="DATE	A	B	C
2021-05-19	14	33	123
2021-05-18	45	12	456
2021-05-17	4	6	213
2021-05-16	5	8	564
2021-05-15	4	9	987
2021-05-14	4	0	543"
| multikv forceheader=1
| fields - _raw linecount
| eval _time=DATE
| fields - DATE
| addtotals
| stats sum(*) as *
| foreach *
    [| eval Percentage_<<FIELD>>=100*<<FIELD>>/Total]

You can remove Percentage_Total as this will always be 100

[ITWhisperer]

Alternatively

| makeresults 
| eval _raw="DATE	A	B	C
2021-05-19	14	33	123
2021-05-18	45	12	456
2021-05-17	4	6	213
2021-05-16	5	8	564
2021-05-15	4	9	987
2021-05-14	4	0	543"
| multikv forceheader=1
| fields - _raw linecount
| eval _time=DATE
| fields - DATE
| addcoltotals 
| addtotals
| foreach *
    [| eval Percentage_<<FIELD>>=100*<<FIELD>>/Total]
| where isnull(_time)
| fields - _time

[schou87]

Hi @ITWhisperer , thanks for that!! But it is returning 8 rows where i just want below (numbers are hypothetical)

A    96.51

B     3.43

C     0.05

[ITWhisperer]

8 rows? There should only be one row where the date is null - this is the row generated by the addcoltotals - if you want to be more specific you could do this

| makeresults 
| eval _raw="DATE	A	B	C
2021-05-19	14	33	123
2021-05-18	45	12	456
2021-05-17	4	6	213
2021-05-16	5	8	564
2021-05-15	4	9	987
2021-05-14	4	0	543"
| multikv forceheader=1
| fields - _* linecount
| fields - DATE
| addtotals
| addcoltotals labelfield=Result label=Total
| foreach *
    [| eval Percentage_<<FIELD>>=100*<<FIELD>>/Total]
| where Result="Total"

[schou87]

I got your point on the ISNULL. Sorry I missed that part.

But the result is giving me extra columns which i dont want

A B C Percentage_A Percentage_B Percentage_C Percentage_Total Total

76

68

2886

2.5082508250825084

2.2442244224422443

95.24752475247524

100

3030

I just want to keep the percentage values 

Percentage_A Percentage_B Percentage_C

2.5082508250825084

2.2442244224422443

95.24752475247524

[ITWhisperer · Accepted solution]

| makeresults 
| eval _raw="DATE	A	B	C
2021-05-19	14	33	123
2021-05-18	45	12	456
2021-05-17	4	6	213
2021-05-16	5	8	564
2021-05-15	4	9	987
2021-05-14	4	0	543"
| multikv forceheader=1
| fields - _* linecount
| fields - DATE
| addtotals
| addcoltotals labelfield=Result label=Total
| foreach *
    [| eval Percentage_<<FIELD>>=100*<<FIELD>>/Total]
| where Result="Total"
| fields - Percentage_Total
| fields Percentage_*

[schou87]

Awesome!! Thank you @ITWhisperer for your patience with me. 🙂