Hello All,
I am trying to create chart table with the below data, I have the table sorted with Month Name from descending order(rolling 13 months) with below search, but i am looking to have month(rolling 13 months) sorted in descending order in x axis, i used Chart in search but with no luck, i have spent lot of time trying different ways but no luck.
I have added what i have tried, sample input and desired output
Request you to kindly help me here.
Below is the Base search where month field is derived after several appends and summarized by stats, it is in string format.
| table ex_p month id Value
| chart values(Value) as final_value by ex_p,id
|sort - id
-----------------------------------------------------
| table ex_p month id Value
| sort - id
| chart values(Value) as final_value by ex_p,month
SAMPLE INPUT
ex_p | month | id | Value |
P1 | Apr-22 | 202204 | 10 | 10% |
P2 | Apr-22 | 202204 | 20 | 15% |
P3 | Apr-22 | 202204 | 100 | 60% |
P4 | Apr-22 | 202204 | 27 | 100% |
R P1 | Apr-22 | 202204 | 12 | 45% |
R P2 | Apr-22 | 202204 | 36 | 89% |
R P3 | Apr-22 | 202204 | 16 | 30% |
R P4 | Apr-22 | 202204 | 28 | 65% |
P1 | Mar-22 | 202203 | 90 | 90% |
P2 | Mar-22 | 202203 | 57 | 120% |
P3 | Mar-22 | 202203 | 18 | 125% |
P4 | Mar-22 | 202203 | 76 | 76% |
R P1 | Mar-22 | 202203 | 80 | 70% |
R P2 | Mar-22 | 202203 | 78 | 99% |
R P3 | Mar-22 | 202203 | 97 | 85% |
R P4 | Mar-22 | 202203 | 08 | 09% |
… | … | … | … |
… | … | … | … |
… | … | … | … |
RP4 | 21-Apr | 202104 | 10 | 110% |
Required OUTPUT
ex_p | Apr-22 | Mar-22 | … | 21-Apr |
P1 | 10 | 10% | 90 | 90% | … | … |
P2 | 20 | 15% | 57 | 120% | … | … |
P3 | 100 | 60% | 18 | 125% | … | … |
P4 | 27 | 100% | 76 | 76% | … | … |
R P1 | 12 | 45% | 80 | 70% | … | … |
R P2 | 36 | 89% | 78 | 99% | … | … |
R P3 | 16 | 30% | 97 | 85% | … | … |
R P4 | 28 | 65% | 08 | 09% | … | … |
Chart will sort the column names lexicographically so you need to take this and resort it - to do this you need to transpose the table
| chart values(Value) as final_value by ex_p,month
| transpose 0 header_field=ex_p column_name=ex_p
| eval ex_p=strftime(strptime("01-".ex_p,"%d-%b-%y"),"%Y%m")
| sort -ex_p
| eval ex_p=strftime(strptime(ex_p."01","%Y%m%d"),"%b-%y")
| transpose 0 header_field=ex_p column_name=ex_p
This can made slightly quicker if you chart by id instead of month, then you don't need the first eval ex_p ... since all this is doing is reconstructing the id from the month.
Chart will sort the column names lexicographically so you need to take this and resort it - to do this you need to transpose the table
| chart values(Value) as final_value by ex_p,month
| transpose 0 header_field=ex_p column_name=ex_p
| eval ex_p=strftime(strptime("01-".ex_p,"%d-%b-%y"),"%Y%m")
| sort -ex_p
| eval ex_p=strftime(strptime(ex_p."01","%Y%m%d"),"%b-%y")
| transpose 0 header_field=ex_p column_name=ex_p
This can made slightly quicker if you chart by id instead of month, then you don't need the first eval ex_p ... since all this is doing is reconstructing the id from the month.
Thank you very much, this is working like a charm
below is the results i am getting, one last ask, can the last column [row 16] be made as 1st column?
serv_perf | Apr-22 | Mar-22 | … | .. | row 16 |
row 1 | 10 | 10% | 90 | 90% | … | .. | P1 |
row 2 | 20 | 15% | 57 | 120% | … | .. | P2 |
row 3 | 100 | 60% | 18 | 125% | … | .. | P3 |
row 4 | 27 | 100% | 76 | 76% | … | .. | P4 |
row 5 | 12 | 45% | 80 | 70% | … | .. | R P1 |
row 6 | 36 | 89% | 78 | 99% | … | .. | R P2 |
row 7 | 16 | 30% | 97 | 85% | … | .. | R P3 |
row 8 | 28 | 65% | 08 | 09% | … | .. | R P4 |
It looks like you have one or both of the transposes wrong - can you share your search?
My Bad, I am sorry for not checking thrice. it is all good. You just made my day.