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 |
| 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_*
| eventstats sum(Status_*) as Total_*
| foreach Status_*
[| eval Percentage_<<MATCHSTR>>=100*<<FIELD>>/Total_<<MATCHSTR>>]
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.
| 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
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)
| 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
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
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
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"
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 |
| 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_*
Awesome!! Thank you @ITWhisperer for your patience with me. 🙂