Jump to content
We've recently updated our Privacy Statement, available here. ×

Dividing two cumulative sums in a cross table


James S 2

Recommended Posts

Hi there,

I have a cross table with two cumulative sums - one for sales and one for target. I want to divide the cumulative sales into the cumulative target, can anyone advise how I do this

Thanks

James

Sum([sales]) THEN Sum([Value]) OVER (AllPrevious([Axis.Rows])) as [sALES],

Sum([Target]) THEN Sum([Value]) OVER (AllPrevious([Axis.Rows])) as [TARGET]

Link to comment
Share on other sites

Hi James,

I have yet to find a graceful way to do this. Two solutions I know are:

 

Do the entire sum again dividing one over the other. I.e. add a comma to the end of your statement and do it all again.

Create these numbers as calculated columns in the data table instead.Usethe equivalent column (Date) instead of Axis.Rows in the AllPrevious function (you may need to use the Intersect function too). Then create another column which is just one divided by the other or do it in the cross table. Keep an eye on how your aggregations are working.

 

Regards,

Jason

Link to comment
Share on other sites

  • 1 year later...

Hi Jason, I was just wondering how your option 1) would also work for in-db connections. My 

 

My two expressions look like this:

 

a)UniqueCount([MARKING_SID]) * 1.0 THEN SUM([Value]) OVER (AllPrevious([Axis.X]))

 

b)Max([CNT_DP]) * 1.0 THEN SUM([Value]) OVER (AllPrevious([Axis.X]))

 

Unfortunately just diving a) by b) does not work. Do you have any suggestions Thanks a lot!

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...