Question Re: Evaluate record for multiple conditions in Technology Q&A

Evaluate record for multiple conditions

former_member795819 — Thu, 05 Jan 2023 22:25:26 GMT

Using webi 4.2:

Each record contains

I've created a variable, Category, using if conditions, but it only evaluates the record once. For example, ID 24 is identified as only New when it should be identified as both New and Termed.

=If [EffDate] >='1/1/2022' And [EffDate]<='12/31/2022' Then "New" ElseIf [ExpDte] >='1/1/2022' And [ExpDte]<='12/31/2022' Then "Termed" Else "NA"

I need to identify each ID as to whether the state was newly added during 2022 and also whether the state was termed in 2022 (Category). The resulting table would look like:

Then for each state, I need to count the number of categories and format as follows:

Any help in figuring this out would be appreciated.

Re: Evaluate record for multiple conditions

krisa24 — Fri, 06 Jan 2023 08:09:54 GMT

Hi,

lets create variable Transaction Type:

[Transaction Type] = If Year(EffDte) = 2022 Then "New"

Else If  Year(ExpDte) = 2022 Then "Termed"

Then you create variable count, however I am missing unique ID in your data set.

Total = count[unique_id]

Then you can display the data in cross table, with

Rows = [Transaction Type]

Columns = [State]

Body = [Total]

The question is, how should be called the cases where the state was newly added during 2022 and also was termed in 2022? They fall under "New" my solution.

Re: Evaluate record for multiple conditions

former_member795819 — Fri, 06 Jan 2023 16:01:27 GMT

Thank you. I've edited my question for clarity. I need to evaluate each record multiple times. ID 24 for AK needs to be identified as New and Termed.

Re: Evaluate record for multiple conditions

krisa24 — Mon, 09 Jan 2023 07:59:55 GMT

I see.

Then you need to create 2 variables:

[New] = If Year(EffDte) = 2022 Then "yes"

[Termed] =  If  Year(ExpDte) = 2022 Then "yes"

And 2 measures:

[Count_New] = Count([ID]) WHERE [New] = "yes"

[Count_Termed] = Count([ID]) WHERE [Termed] = "yes"

Then you create 2 horizontal tables where:

Table 1

column = [State]

Body = [Count_New]

Table 2

column = [State]

Body = [Count_Termed]

Now click on Table 2 and

select Format Table > Layout and set the position relative to Table 1 (so Table 2 is exactly under the Table 1)
select Format Table > General and hide uncheck Table Headers