Dividing two cumulative sums in a cross table

[James S 2]

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]

[Jason Joslin 3]

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

[Jonas Mlynek 2]

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!