"IF" "AND" "OR" Statement in Set Analysis

[jmcdermott]

In Excel this formula below gets me what I need below.  How can I recreate this in Set Analysis.  Attached is my output of data from Qlikview to Excel (with $ cleared out).  I have 4 fields Column B = "CR" this is a mapping key based on account #, Column C "AffiliateCode|JFormAccount" is company code and rollup account #, column D is "subdivision" based on the cost center mapped from a different mapping table then Account #.  Columns E-G are the Last year year to date(YTD) actuals, YTD Budget, and YTD actuals that I want summed if meets meets the criteria.  I want all info for field "CR", unless is is value "PC" and in that case, I only want information if field subdivision are 1-Direct, or 2-Indirect, or 3-Engineering, or 8-UTC.  

=IF(AND(B2="PC",OR(D2="4-OUTSIDE",D2="5-INSIDE",D2="6-SHIPPING & WAREHOUSE", D2="8-ADMINISTRATION")), "-",B2)

Thanks,

Josh

[rodrigo_martins]

Hello,

There are some ways to solve this problem.
The first would be to place this Excel rule in a column in your data model, creating a flag field. This way, the Set Analysis expression would be very simple:

{<[FlagField]={1}>}

You could even use an IF to build this rule in the transformation step. This approach is interesting if you have a large volume of data, because it removes the complexity of the calculation from the front-end. The disadvantage is that you are not always able to change something in the transformation stage, in addition to having to change the code whenever the rule changes. Furthermore, if the end user needs a different criterion, they will not have the resources to modify it.

The other approach is to use Set Analysis directly on the table, as you initially asked. You can link it to a variable, so that the user can modify the values ​​of the fields that form the created criteria. However, this solution depends on the existence of a unique attribute in the data row (such as an ID). This way, the Set Analysis would look like this:

{<ID=E({<[CR]={'PC'}, [subdivision]={'4-OUTSIDE', '5-INSIDE', '6-SHIPPING & WAREHOUSE', '8-ADMINISTRATION'}>} [ ID])>}

The E function will generate a subset of IDs that do not match the filters established in the Set Analysis inside. It is complementary to the P function, which generates the subset of possible values ​​of the ID field that meet the Set Analysis. More information here.

There are several ways to generate a unique identifier attribute: you can, for example, concatenate columns to form a key (columns that generate a unique combination for each row), using the AutoNumber function to improve overall performance.

Hope this helps!