How to sum distinct dimensions

[#1]

Hi there,

I have a table like this:

Year	Month	Field 1	Field 2	Field 3	Vol
2012	11	S	G	L	100
2012	11	S	O	L	150
2012	11	S	G	C	100
2012	11	S	O	C	150
2012	11	S	P	C	200
2013	1	S	G	L	100
2013	1	S	O	L	75
2013	1	S	G	C	100
2013	1	S	O	C	75
2013	1	S	P	C	200
2013	1	S	S	C	75

And I want to get the sum of all the distincts Field 2, like this

Year	Month	Field 2	Vol
2012	11	G	100
2012	11	O	150
2012	11	P	200
Total			450
2013	1	G	100
2013	1	O	75
2013	1	P	200
2013	1	S	75
Total			450

to create a table which displays the sum (vol) by Field 3 and the Total Vol (sum all distincts Field 2) by year, exactly like this:

Field 1	Field 3	Year - Month	Vol	Total Vol
S	L	2012 - 11	250	450
S	C	2012 - 11	450	450
S	L	2013 - 01	175	450
S	C	2013 - 01	450	450

I've tried

Total vol = sum(TOTAL aggr(sum(distinct Vol),Field 1,Field 2) )

but only works when I select a year and a month

Thanks,

Bere

[#2]

try this code

take a pivot table--

Dimension is Year, Field1, Field2, Field3, Month

in expression

sum(Aggr(sum(DISTINCT Vol),[Field 2],Year))

then in presentation tab select Field2 and then check on Partial sum

then output like this

Year	Field 1	Field 2	Field 3	Month	Vol
2012	S	G	L	11	100
2012	S	O	L	11	150
2012	S	P	C	11	200
2012	S	Total			450
2013	S	G	L	1	100
2013	S	O	L	1	75
2013	S	P	C	1	200
2013	S	S	C	1	75
2013	S	Total			450

see ttachement

[kaushiknsolanki]

Hi,

     Try this.

     Create a chart with dimensions as Field1,Field2,Year,Month

     And expression as

     Vol : - Sum(Vol)

     Total Vol : - Sum(Total <Year> Vol)

Regards,

Kaushik Solanki