Evaluate record for multiple conditions

[former_member795819]

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.

[krisa24]

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.

[former_member795819]

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.

[krisa24]

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