I have the following displayed
FEB-2016 March-2016 April-2016 May-2016 Application TechStack
2 2 2 2 ABC JAVA
3 3 3 3 XYZ JAVA
4 4 4 34 XYY JAVA
2 2 2 2 XYU JAVA
3 3 3 3 XYP DOTNET
3 3 3 3 XYI DOTNET
I created this using xyseries. Now I want to calculate the subtotal of hours (the number mentioned is basically the hours) by TechStack. I have already applied appendpipe to subtotal the hours, but the subtotal value is not being displayed. I only get blank spaces. How do i do it?
...|appendpipe [stats sum(*) as * by TechStack |eval Application="All Applications"]
will something like this work, then?
Here's run-anywhere code that proves that it will. This produces his test data in a monthly format by Aplication and TechStack
|makeresults
| eval mydata="JAVA!!!!ABC!!!!2,2,2,2 JAVA!!!!XYZ!!!!3,3,3,3 JAVA!!!!XYY!!!!4,4,4,34 JAVA!!!!XYU!!!!2,2,2,2 DOTNET!!!!XYP!!!!3,3,3,3 DOTNET!!!!XYI!!!!3,3,3,3"
| makemv mydata | mvexpand mydata | makemv delim="!!!!" mydata
| eval TechStack=mvindex(mydata,0), Application=mvindex(mydata,1), mydata=mvindex(mydata,2)
| makemv delim="," mydata
| eval monthdata=mvappend("2016-02,".mvindex(mydata,0),"2016-03,".mvindex(mydata,1),"2016-04,".mvindex(mydata,2),"2016-05,".mvindex(mydata,3)) | table TechStack, Application, monthdata |mvexpand monthdata| makemv delim="," monthdata | eval Month = mvindex(monthdata,0), Count= mvindex(monthdata,1)| table Month, TechStack, Application, Count
This turns that into a table that looks like his output. (I suspect that the original poster created a crosstab or xyseries by Application and then appended the column for TechStack.)
| eval series=Application." ".TechStack
| xyseries series,Month,Count
| makemv series
| eval Application=mvindex(series,0),TechStack=mvindex(series,1)
| table *20* Application TechStack
And then run this to prove it adds lines at the end for the totals
| appendpipe [stats sum(*) as * by TechStack | eval Application = "Total for TechStack"]
And, optionally, sort into TechStack, Application, Totals order
| appendpipe [stats sum(*) as * by TechStack | eval Application = "zzzz"]
| sort 0 TechStack Application
| eval Application=if(Application="zzzz","Total for TechStack",Application)
Or, optionally, sort into TechStack, Application, Totals order and add visual spacers after TechStack subtotals
| appendpipe [stats sum(*) as * by TechStack | eval Application = "zzzz1"]
| appendpipe [stats count by TechStack | eval Application = "zzzz2" |table Application TechStack]
| sort 0 TechStack Application
| eval TechStack=if(Application="zzzz2","",TechStack)
| eval Application=case(Application="zzzz1","Total for TechStack",Application="zzzz2","",true(),Application)
can we see the query?
can you do something like this instead of appendpipe:
...|eventstats sum(*) as sum* by TechStack |fields - sumApplication
Well I cannot show you the query but I can tell you that I created this display using xyseries and mvindex.i need to calculate the subtotal using appendpipe only.By the way thanks for the answer.If you could do it using appendpipe please let me know