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

Keep empty/null in if-then calculation

Marcel Reinert ·

Hi,

I have matrix table with dimensions and in there values till 100% and also some empty/null lines because there is no entry for some specific dimension (which is correct). However, our customer now wants there to be only the value 0 or 100. So I create a custom calculation and set everything with value 100 to 100 and everything below to 0 (if sum(0,0,m1) = 100 then 100 else 0). Fits. But now it also makes a 0 out of the null/empty values. How do I get it to keep the empty rows and not make a 0 out of NULL?

tia,

Marcel

1

Comments

5 comments
Sort by DateVotes
  • Niels Thomsen

    Hi Marcel

    You can test if a cell is empty with the count function.

    Next problem is to keep it empty, since the calculation engine can't return nulls (or spaces).

    However you can hide the values that are empty with some visibility agent logic.

    .1. First you make a change to your calculation like this:
    if count(d1,0,m1) = 0 then 999 else if sum(0,0,m1) = 100 then 100 else 0

    This means empty cells will be assigned the value 999 and if they are not empty, your logic is applied.

    2. Now make a visibility agent that only hides values (not members). 

    Make a condition saying:
    value=999

    That should do it.

    br/Niels

    2
  • Marcel Reinert

    Thank you Niels. The tip with the count function helped me out.

    0
  • Morten Færk Rygaard

    I am experiencing an issue similar to the one Marcel describes in his original post here. Mine differs though in the way that the calculation i need to hide is being done as a column, and it doesn't look like visibility agents can be set on calculated columns? My table looks like the one below
    The columns are based on the "Akk.sum Forbrug" calculated measure which is an accumulated sum of spending for a given month/year. The columns then does a static multiplication of a years values to apply an inflation value to it like this:
    sum(d-3, 0, m3) * 1,073271 for the 2022 column.
    sum(d-2, 0, m3) * 1,041 for the 2023 column and
    sum(d-1, 0, m3) for the 2024 column.
    2024 however flatlines for  months that has not yet occured, and i would like to hide these months. I can edit the fomular for 2024 to something like:
    if sum(d-1, 0, m3) > sum(d-1, -1, m3) then sum(d-1, 0, m3) else 0
    which handles the months correctly, but just flatlines the graph instead of hiding the values. So I also wish for the ability to return NULLS in a calculation, or some way of hiding values in a calculated column?

    0
  • Louise Stub Hansen

    You mention that "it doesn't look like visibility agents can be set on calculated columns?"

    As far as i can see, I use visibility agents on calculated columns. So I wonder why this isn't available in you case?

    0
  • Morten Færk Rygaard

    Perhaps i am missing something then.

    I have the following calculations on my chart:

    Where 2022, 2023, and 2024 are defined as columns c1, c2 and c3. Then under visibility agents i can only select the measures:

    If I add a new calculated measure, it appears under visibility agents just fine.

    0
Please sign in to leave a comment.