topic Re: % of total in Dashboards & Visualizations

% of total

schou87 — Thu, 20 May 2021 13:01:22 GMT

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

Re: % of total

ITWhisperer — Thu, 20 May 2021 13:14:20 GMT

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

Re: % of total

schou87 — Thu, 20 May 2021 13:25:31 GMT

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.

Re: % of total

ITWhisperer — Fri, 21 May 2021 06:55:52 GMT

Re: % of total

schou87 — Fri, 21 May 2021 08:05:47 GMT

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)

Re: % of total

ITWhisperer — Fri, 21 May 2021 08:13:44 GMT

You can remove Percentage_Total as this will always be 100

Re: % of total

ITWhisperer — Fri, 21 May 2021 08:18:12 GMT

Alternatively

Re: % of total

schou87 — Fri, 21 May 2021 08:32:10 GMT

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

Re: % of total

ITWhisperer — Fri, 21 May 2021 08:43:35 GMT

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

Re: % of total

schou87 — Fri, 21 May 2021 09:15:10 GMT

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

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

Re: % of total

ITWhisperer — Fri, 21 May 2021 09:23:24 GMT

Re: % of total

schou87 — Fri, 21 May 2021 12:36:49 GMT

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