cancel
Showing results for 
Search instead for 
Did you mean: 

How do I display specific data based on a field with multiple values

Former Member
0 Kudos

Hello,

First post, looking for some guidance and could not find my solution in the archives.

I have created a report that brings back data based on a specific field called the Adjustment Control Code (ACNT) which defines the value($) I need displayed. The ACNT field can contain up to 5 different values (Null,1,2,3,and 4). (side note: The field comes back as a string value so I have this option to use as well as a variable I created that converts these values to numbers 0,1,2,3,and 4). What I am trying to return is a single row for each account, based on what code is listed in this ACNT field. The problem is the ACNT field can come back with just a single value, or it can come back with a combination of values such as 2 and 4 or 0,1,2, and 4 as examples. Each time a different ACNT code is presented, it creates a new row to display. The problem is if the ACNT combination contains a 2, I only need to see the ($) value associated with that row. So with the situation where the account brings back ACNT codes Null,1,2,and 4, I only want the row in relation to the ACNT 2.

This is the variable I created in an attempt to bring back the $ value needed based on specific combinations of possible ACNT codes that could be presented:


=If([Adjustment Control Code])=("2") Then ([THPB List (Calc_-6)])

ElseIf ([Adjustment Control Code]) InList("2";"4") Then ([THPB List (Calc_-6)]) ElseIf ([Adjustment Control Code]) InList("2";"1") Then ([THPB List (Calc_-6)])  ElseIf ([Adjustment Control Code]) InList("2";"4";"1") Then ([THPB List (Calc_-6)]) Else ([Unit Price (Calc_65)])

It seems as if a filter would do the trick, but it has to be built with the conditional requirements above, not to exclude the other values if a 2 is not present.

Please advise.

Thanks

Kyle

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

This is still an open topic that is not answered, any help is greatly appreciated.

thanks

Kyle

Former Member
0 Kudos

.

Former Member
0 Kudos

Hi Kyle,

try this :

1 - Create a measure variable [Test1] = =If([ACNT])="2";1;0);

2 - I´m calling the dimension that "contains" the ACNT values, [abc],

measure variable [Test2] = Sum(If((Sum([teste1]) Em ([abc])=0);1;0))

3 create a thrid measure variable, [filter] = If ( ( [Test1] =1 ) OR ( [Test2] = 1);1;0)

If you filter your report by [filter] = 1 you finish with the records that :

1 If there is more than one value of [ACNT] and one of those is 2 will keep ony the '2'

2 If there is no '2' will keep all values

I think that is yourrequirement, right ?

Cheers,

Rogerio

Former Member
0 Kudos

Hello Rogerio,

Thanks for looking into this for me.

Question for you on your Test2 dimension. What is the Em?

"=Sum(If(Sum([Test1]) Em ([abc])=0);1;0))"

I assumed it was a function, but when in create variable, I get an "invalid identifier" because of it.

thanks

Kyle

Former Member
0 Kudos

Sorry Kyle,

I´m from Brazil an Em means In

so the variable is

=Sum(If(Sum([Test1]) In ([abc])=0);1;0))"


Terrible sorry, My bad !!!!


Regards,

Rogerio

Former Member
0 Kudos

Rogerio,

No problem at all. changing it to "In" allowed the formula for Test2 to pass.

After creating the following variables as directed:

     {Test1} =If(([Adjustment Control Code])="2";1;0)

     {Test2} =Sum(If(Sum([Test1]) In([THPB List (Calc_-6)])=0;1;0))

     {Filter}  =If(([Test1])=1 Or ([Test2])=1;1;0) 

Then applied the filter of 1 to the filter variable.

It entered the value of 1 for all 4 lines See below

:

Also, if I remove the ACNT field from the output as the values here are not needed on the output, the Filter variable comes back as #MULTIVALUE instead of the value of 1 or 0

What did I do incorrectly?

Former Member
0 Kudos

Hi,

can you please add Test1 and Test2 to the table as well just to see what it brings ?

As you use the ACNT object in you calculation, if you remove it will givwe you multivalue.

You must add a ForEach.

Can you share your original data, at least an excerpt of it?

Regards,

Rogerio

Former Member
0 Kudos

Rogerio,

What original data would you like to see?

This is the image w/ the ACNT still in place

This is the image w/o the ACNT still in place

The ForEach makes sense, but where exactly in the formulas will it be applied?

Thanks

Kyle