Jump to content

over function with empty rows

Recommended Posts

we have created a crosstable with time (months) as columns and products (SKU) as rows summing the volumes (units).

Next to showing monthly values we also want to show accumulated values like Year to Date. We do this using the "over" function in the view:

Sum([units]) THEN Sum([Value]) OVER (Intersect(AllPrevious([Axis.X]),NavigatePeriod([Axis.X],"Year",0,0)))

Some of the products do not have volume in a particular month and we experience that the over function excludes the complete product then

Eg a product has volume in March (120) and May (150) only. We would like to see the YTD shown as 0 until Feb and then 120 until May and then 270 onwards. Instead the value is NULL when there are no datapoints (these rows do not exist in the underlying datatable for the months without volume)

We tried several options with no results. How can we overcome this issue and ensure the volume are correct when calculating YTD or moving annual total?


Link to comment
Share on other sites

dear Gaia,

would it be possible to have virtual call or DM you so we can show you some things? We found out yesterday that a remake of the same crosstable is working properly but we do not understand why the old one has issue.

We also noticed that the new crosstable "merges" the vertical axis where the old crosstable repeats each line-item.

Would be great to have quick catch-up.



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...