Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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