1. TARGIT Community
  2. Community
  3. Frontend, Designer and Consumer
Knowledge Base

Hiding row members based on calculation

Rasmus Remmer Bielidt ยท

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.

0

Comments

4 comments
Sort by DateVotes
  • Marlene Knaup

    Hi Rasmus Remmer Bielidt

    I think the v in the fomular is the cause of it not showing up in the visibility agent.

    0
  • Rasmus Remmer Bielidt

    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.

    if sum(d1:d-14, 0, m1) < 0.01 and count(d-13:d-1, 0, m1) = 0 then 1 else 0

    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.

    0
  • Ole Dyring

    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

    0
  • Rasmus Remmer Bielidt

    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

    0
Please sign in to leave a comment.