Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My data is has following cols
Country, State, Period(in mm/dd/yyyy format), Qty, Year
A, A1, 1/1/2015, 10, 2015
A, A2, 1/1/2015, 20, 2015
A, A1, 2/1/2015, 25, 2015
A, A1, 1/1/2014, 15, 2014
A, A1, 2/1/2014, 10, 2014
B, B1, 3/1/2015, 30, 2015
I want to achieve the following output
Year Country Sum(Qty) (for the Year) Cumulative % (at Year level)
2015 A 10+20+25=55 =55/(55+30)
2015 B 30 =30/(55+30)
2014 A 15+10=25 =25/(25)
Any help much appreciated.
Thanks,
Gopal
Sum (Qty) / Sum (total <Year> Qty)
Sum (Qty) / Sum (total <Year> Qty)
Create a chart with dimensions Year and Country.
Then first expression
= Sum(Qty)
and as second
= Sum(Qty) / Sum(TOTAL<Year> Qty)
Thanks! I was trying the same all this time but it wasn't working. I figured out that for the above to work, the first col. of the output HAS to be a Dimension (in the input data) and it can't be a dimension expression.
Earlier my first col. would be calculated dimension as Year(Period). Now with this in. the above
Sum (Qty) / Sum (total <Year> Qty) would still calculate the % against total across all years. So I even tried
Sum (Qty) / Sum (total <Yea(Period)r> Qty) - even this didn't work. Finally had to resort to change my dimension from a calculated one to a direct Year dimension and that's when the formula worked.
Is this expected behavior?