over function with empty rows

[Administratie Spot-On Insights]

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?

Etienne

[Gaia Paolini]

Please can you share a dxp with anonymised data?

[Administratie Spot-On Insights]

dear Gaia,

thank you for your response. I will share something by tomorrow.

thank you for your patience.

Etienne

[Administratie Spot-On Insights]

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.

thanks

Etienne

[Fredrik Rosell]

Hello!

This is moving beyond what can be provided in the community and into the realm of support, so I recommend that you open a support case in the TIBCO Support Portal (https://support.tibco.com) and our Support team will assist you.

Best Regards

Fredrik (TIBCO Support)