Dashboards & Visualizations

How to chart order by year & month

punithgubbi
Explorer

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%
Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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.

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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.

punithgubbi
Explorer

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_perfApr-22Mar-22..row 16
row 110 | 10%90 | 90%..P1
row 220 | 15%57 | 120%..P2
row 3100 | 60%18 | 125%..P3
row 427 | 100%76 | 76%..P4
row 512 | 45%80 | 70%..R P1
row 636 | 89%78 | 99%..R P2
row 716 | 30%97 | 85%..R P3
row 828 | 65%08 | 09%..R P4
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

It looks like you have one or both of the transposes wrong - can you share your search?

0 Karma

punithgubbi
Explorer

My Bad, I am sorry for not checking thrice. it is all good. You just made my day.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...