I'm having a trouble arranging my columns per month.
I want it to the be arranged like this:
|Sept-15-2017| |Sept-30-2017| |Sept-2017| |Oct-15-2017| |Oct-30-2017||Oct-2017|
Is there a way where i can arrange the columns like the example above?
Hi @patricianaguit,
Can you use sort command before your table command
...| sort output| table output,Target,Actuals,......
It seems that we have to define all the tables like this.
(your search)|table Sept-*-*,Sept-*,Oct-*-*,Oct-*
@patricianaguit, Can you add your current search which generates above table?
already included my current search!
@patricianaguit please use the code button (101010)
for adding data and code so that special characters do not escape
this is my current search
index="acn_pmo_capacity_chargeability_idx"
| table *
| fields "Enterprise ID" "Employee Level" Date "TR Period" "Charge Code" "Charge Code Description" Hours
| fields - _raw _time
| eval Date=strptime(Date, "%m/%d/%Y"), Month=strftime(Date, "%b-%Y")
| sort Date
| eval Date=strftime(Date, "%m/%d/%Y")
| rename "Enterprise ID" AS EID, "TR Period" as TRPeriod
| eval date1 = strptime(TRPeriod, "%m/%d/%Y"), TRPeriod = strftime(date1, "%b-%d-%Y")
|eval output = Month + ";" + TRPeriod |
makemv delim=";" output
| mvexpand output
| sort "EID"
| lookup wbs_lookup "Charge Code" outputnew WBS
| where WBS = "Non-Chargeable" OR WBS = "Chargeable" OR WBS = "Training"
| stats sum(eval(case(WBS == "Chargeable", Hours, 1=1,null()))) as TotalChargeableHours, sum(Hours) as TotalHours by EID, output
| fillnull value=0.00
| eval Chargeability = round((TotalChargeableHours / TotalHours) * 100, 2)
| sort EID
| fillnull value=0.00
| fields EID, output, Chargeability
| xyseries output EID Chargeability
| fields - _timediff
| table output, *
| transpose 0 header_field=output
| appendpipe [stats avg(*) as * | foreach * [eval "<>"=round('<>', 2)] | eval column="Actuals"]
| untable column output Chargeability
| rename column AS EID
| xyseries output EID Chargeability
| eval Target = "70%"
| table output, Target, Actuals, *
| eval date = strptime(output, "%b-%d-%Y")
| sort date
| fields - date, _timediff
| table output, Target, Actuals, *
| transpose 0 header_field=output
| fillnull value=0.00
| rename column as EID
| lookup Roster_lookup.csv EID outputnew "Career Level"
| table EID "Career Level" *
| transpose 0 header_field=EID
| eval getmonth=strptime(column,"%b-%e-%Y")
| fillnull value=0.00
| sort getmonth
| fields - getmonth
| transpose 0 header_field=column
| rename column AS EID
| eval "Career Level"=if(EID="Target" OR EID="Actuals", "", 'Career Level')