Why does using the by and over clauses not work simultaneously with the chart command?

[amitmenon123]

Hello guys,

i have a csv file with rows

Resource    Contract Category   Sub Activity   Team    Activity    Description  5-January-2017     12-January-2017    19-January-2017    26-January-2017 

where the fields with January are basically week wise expansion of hours of a resource. I wanted the sum of hours in the whole month of January based on each contract category for every resource. Also there are multiple entries for same value of resource and contract category like

Resource  Contract Category   Team  5-January-2017  12-January-2017
   A        MT                 Ab          0              1
   A        MT                 Ab          1              0

So

source = "Mysource.csv" host = "M2B-D-80NCVQ1" sourcetype = csv Resource="*" "Contract Category"="*" | chart sum(*Jan*) as *Jan* ,sum(*Feb*) as *Feb* over "Contract Category"| addtotals *Jan* fieldname="January" | addtotals *Feb* fieldname="February"| table "Contract Category",January, February | addtotals

gives the total hours by perfectly

but

source = "Mysource.csv" host = "M2B-D-80NCVQ1" sourcetype = csv Resource="*" "Contract Category"="*" | chart sum(*Jan*) as *Jan* ,sum(*Feb*) as *Feb* over "Contract Category" by Resource | addtotals *Jan* fieldname="January" | addtotals *Feb* fieldname="February"| table "Contract Category",January, February | addtotals

doesn't give a statistics or visualization. Theres no error for the search either
How can i get

Contract Category January February Total hours for every resource in the sheet?

[amitmenon123]

source = "Copy of ETT-newc3.csv" host = "M2B-D-80NCVQ1" sourcetype = csv Resource = "" "Contract Category" = "" | stats list( January_) as January_,list(February_) as February_ by Resource  Team  "Contract Category"

gives the right classification. just that instead of list when i sum it. it doesn work can anyone help/

[somesoni2]

Try like this

source = "Mysource.csv" host = "M2B-D-80NCVQ1" sourcetype = csv Resource="*" "Contract Category"="*" | stats sum(*Jan*) as *Jan* ,sum(*Feb*) as *Feb* by "Contract Category"  Resource | addtotals *Jan* fieldname="January" | addtotals *Feb* fieldname="February"| table "Contract Category",January, February | addtotals

In the chart you specify only one aggregation fields, whether using over or by clause, you get a linear output with one row for each value of the aggregation field. E.g. in your first example you'll get a row for each "Contract Category". When you specify two aggregation fields in chart command, either using over field1 by field2 or by field1 field2, there will one row for each value of field1 and there will one or more column for each value of field2. So, in your second attempt, There would be one column for each combination of value of Resource and aggregate function. See Splunk documentation for more details.

http://docs.splunk.com/Documentation/Splunk/6.5.2/SearchReference/Chart

[amitmenon123]

This didnt work though,. the clarity on charting is appreciated. But i think it is basically related to the way we are proceeding. Since my table has multiple entries with same name team and contract category, the problem is in directly summing it by resource or contract category and trying anotherr level of grouping. For eg.

source="Mysource.csv" host="M2B-D-80NCVQ1" sourcetype="csv" Resource="*" NOT Resource="Grand Total"|chart list(Team),list("Contract Category") as "Contract Category", list(*Jan*) as *Jan* ,list(*Feb*) as *Feb* by Resource

Gives me the exact table entries as it is in the sheet for the resource. Now i can proceed to sum it by Contract Category and then by team. The problem i am still facing is it doesnt iterate for every resource like

source="Copy of ETT-newc2.csv" host="M2B-D-80NCVQ1" sourcetype="csv" Resource="*" NOT Resource="Grand Total"|chart list(Team),list("Contract Category")  as "Contract Category", list(*January*) as *January* ,list(*February*) as *February*  by Resource| chart sum(*January*) as *January* ,sum(*February*) as *February* by "Contract Category",Resource

doesnt work and

source="Copy of ETT-newc2.csv" host="M2B-D-80NCVQ1" sourcetype="csv" Resource="*" NOT Resource="Grand Total"|chart list(Team),list("Contract Category")  as "Contract Category", list(*January*) as *January* ,list(*February*) as *February*  by Resource| chart sum(*January*) as *January* ,sum(*February*) as *February* by "Contract Category"

again gives total hours not by resource

[amitmenon123]

i think the answer is in subearches but i want to figure out a way to pass values in subsearches like
for each resource and for each team of resource add up the weekly hours first then sum it by months