6 Replies Latest reply on Aug 20, 2018 8:29 AM by Okechukwu Ossai

# Merge Calculated Fields?

Hi,

I have created 4 Calculated Fields with same column names containing different values for respective Fields. I would like to know if there is a way to merge all the 4 calculated field into one.
For example, here you can see one of the calculated fields name Flags1 with values Null, 1, 2, 3,4,5,6. Similarly, I have other fields namely Flags2, Flags3, Flags4.

Is there a way to merge all the Flags attribute into one and show it as a cumulative graph.
Thanks
Jasim • ###### 1. Re: Merge Calculated Fields?

Not sure I understand your requirement fully. However, if the different flag fields are all of the same data type and are comparable. You could try;

Flags1+Flags2+Flags3+Flags4

• ###### 2. Re: Merge Calculated Fields?

thanks for the reply.

I am looking for something like this:
I have created 4 calculated field (flags1, flags2, flags3, flags4) and got its respective count. For example:

Flags1:
Null     1000
1     20

2     10

3     5

4     10

Flags2:

Null     500
1     10

2     20

3     10

4     50

Flags3:

Null     1000
1     10

2     20

3     30

4     10

Flags4:

Null     1000
1     20

2     20

3     10

4     10

I am looking for something like this:

Cummulative
Null     3500

1     60

2     70

3     55

4     80

As you said I tried  Flags1+Flags2+Flags3+Flags4, but I am only getting the null values.

• ###### 3. Re: Merge Calculated Fields?

Ok, may need to understand the data structure a bit. Are these flags all on the same row level?

Can you drag Flags1, Flags2, Flags3 and Flags4 to the Rows shelf and show me how they appear?

• ###### 5. Re: Merge Calculated Fields?

Hi Jasim,

zn(flag 1) + zn(flag 2) etc. should do it for you, this will return a 0 instead of a Null where there is one, and not Null the rest of the calculation.

• ###### 6. Re: Merge Calculated Fields?

Hi Jasim,

This is not straightforward because the fields are not at the same row level, so you can't perform a direct addition of the fields. You can use LOD to force the numbers to match at same row level and then sum them.

I'm assuming the data type of Flags1, Flags2 etc is Number(whole). If they are strings then you will have to modify the formula below. The formula is for when Flags1 is 1, 2, 3, 5, 6 and null

Create calculated field [Combined Flag]

IF [Flags1] = 1 THEN
{FIXED: MAX({FIXED [Flags1]: COUNT(IF [Flags1] = 1 THEN [Flags1] END)})} +
{FIXED: MAX({FIXED [Flags2]: COUNT(IF [Flags2] = 1 THEN [Flags2] END)})} +
{FIXED: MAX({FIXED [Flags3]: COUNT(IF [Flags3] = 1 THEN [Flags3] END)})} +
{FIXED: MAX({FIXED [Flags4]: COUNT(IF [Flags4] = 1 THEN [Flags4] END)})}

ELSEIF [Flags1] = 2 THEN
{FIXED: MAX({FIXED [Flags1]: COUNT(IF [Flags1] = 2 THEN [Flags1] END)})} +
{FIXED: MAX({FIXED [Flags2]: COUNT(IF [Flags2] = 2 THEN [Flags2] END)})} +
{FIXED: MAX({FIXED [Flags3]: COUNT(IF [Flags3] = 2 THEN [Flags3] END)})} +
{FIXED: MAX({FIXED [Flags4]: COUNT(IF [Flags4] = 2 THEN [Flags4] END)})}

ELSEIF [Flags1] = 3 THEN
{FIXED: MAX({FIXED [Flags1]: COUNT(IF [Flags1] = 3 THEN [Flags1] END)})} +
{FIXED: MAX({FIXED [Flags2]: COUNT(IF [Flags2] = 3 THEN [Flags2] END)})} +
{FIXED: MAX({FIXED [Flags3]: COUNT(IF [Flags3] = 3 THEN [Flags3] END)})} +
{FIXED: MAX({FIXED [Flags4]: COUNT(IF [Flags4] = 3 THEN [Flags4] END)})}

ELSEIF [Flags1] = 5 THEN
{FIXED: MAX({FIXED [Flags1]: COUNT(IF [Flags1] = 5 THEN [Flags1] END)})} +
{FIXED: MAX({FIXED [Flags2]: COUNT(IF [Flags2] = 5 THEN [Flags2] END)})} +
{FIXED: MAX({FIXED [Flags3]: COUNT(IF [Flags3] = 5 THEN [Flags3] END)})} +
{FIXED: MAX({FIXED [Flags4]: COUNT(IF [Flags4] = 5 THEN [Flags4] END)})}

ELSEIF [Flags1] = 6 THEN
{FIXED: MAX({FIXED [Flags1]: COUNT(IF [Flags1] = 6 THEN [Flags1] END)})} +
{FIXED: MAX({FIXED [Flags2]: COUNT(IF [Flags2] = 6 THEN [Flags2] END)})} +
{FIXED: MAX({FIXED [Flags3]: COUNT(IF [Flags3] = 6 THEN [Flags3] END)})} +
{FIXED: MAX({FIXED [Flags4]: COUNT(IF [Flags4] = 6 THEN [Flags4] END)})}

ELSE
{FIXED: MAX({FIXED [Flags1]: COUNT(IF ISNULL([Flags1]) THEN [Number of Records] END)})}+
{FIXED: MAX({FIXED [Flags1]: COUNT(IF ISNULL([Flags2]) THEN [Number of Records] END)})}+
{FIXED: MAX({FIXED [Flags1]: COUNT(IF ISNULL([Flags3]) THEN [Number of Records] END)})}+
{FIXED: MAX({FIXED [Flags1]: COUNT(IF ISNULL([Flags4]) THEN [Number of Records] END)})}

END

Hope this helps.

Ossai

1 of 1 people found this helpful