2 Replies Latest reply: Jul 16, 2013 2:38 AM by Kaushik Solanki

# How to sum distinct dimensions

Hi there,

I have a table like this:

Year
Month
Field 1
Field 2
Field 3
Vol
201211SGL100
201211SOL150
201211SGC100
201211SOC150
201211SPC200
20131SGL100
20131SOL75
20131SGC100
20131SOC75
20131SPC200
20131SSC75

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

• ###### Re: How to sum distinct dimensions

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

• ###### Re: How to sum distinct dimensions

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