Hiding row members based on calculation
I have a cross tab with item ids on rows and dates on columns. I need to present the accumulated sum (on-hand inventory) for each item for the last fourteen days. In the cube only the movement on quantity is available, so I created a calculated measure in TargIT for on-hand using the formula "sum(d1:0,0,m1)". This works well and I get the crosstab as expected.
I hid a range of dates starting from the first to fourteen days before the last (offset by 1 day to exclude "today", but that is besides the point).
However, I would like to exclude those item ids that have all zeroes in the calculated measure for the visible dates and created a calculated column for this purpose using the formula "if sum(all(v),0,m2)>0.01 then 1 else 0" (m2 being the calculated on-hand per date) yielding a 1 if the row should be hidden and a 0 if not.
But I am unable to use the calculated column for a visibility agent. As can be seen from the below, I have only m1 (Qty) and m2 (On-hand qty).
I also did try to use the formula directly as an agent condition, but all that did was to invalidate the formula calculating the column.
Any ideas as to why this happens?
I tried changing the calculation from a column to a measure, but without any change in the behaviour of the visibility agent.
Comments
Hi Rasmus Remmer Bielidt
I think the v in the fomular is the cause of it not showing up in the visibility agent.
Hi Marlene,
Thanks for the pointer. It does indeed seem like the (v) reference parameter is the root cause of the problem.
I can change the formula for the RowVisibility to be less dynamic
e.g.
This gives me an option to hide the entire member based on the value of this calculation (no on-hand quantity at the end of the date in the first column and no transactions during the following 13 days).
But at the same time it is prone to me remembering to change the formula if the number of days displayed changes which, knowing myself, will be somewhat unlikely to happen automatically :D
At best, this is a workaround. I will file a bug report with TargIT.
Hi Rasmus.
You have encountered one of the things that is not possible in TARGIT.
You cannot create a condition or a calculation that depends on visibility (directly or indirectly) if that condition or calculation has the potential of affecting visibility.
BR / Ole
Hi Ole,
Thanks for the response. I can appreciate the problematic nature of layering visibility attributes. I will go with the workaround that I made instead.
BR
Rasmus
Please sign in to leave a comment.