How to use "OR" in the SET Analysis

[#1]

I have created an expression (which works perfectly):

= SUM ({<[User Comments 4WKS] = {"$(vUC4WKSCombined)"}> [User Comments 4WKS])

variable (vUC4WKSCombined) contains values from 0 to 100

but there are also NULL values for dimension [User Comments 4WKS]

Now my question is how can I change my expression so it can take care of both varialbe values from 0 to 100 and also NULL values

I need OR condition between two set modifiers, like dimesion  [User Comments 4WKS] can either take any values from 0 to 100 OR is NULL

= SUM ({<[User Comments 4WKS] = {"$(vUC4WKSCombined)"} or [User Comments 4WKS] = {"NULL"}> [User Comments 4WKS])

Can someone look into this and write the correct experssion?

Thanks,

TA

[#2]

hi

The"+"-sign is used for OR in set analysis

An "OR" in set analysis is a union of two sets. You do a union with the "+" operator like so:

sum({<Year={2012}>+<Region={US,SE,DE,UK,FR}> Sales)

[Gysbert_Wassenaar]

Nulls can't be selected so they're a bother to work with. It's usually a good idea to replace nulls in the script with a value so it can be selected.

But try:

SUM ({<[User Comments 4WKS] = {"$(vUC4WKSCombined)"} + [User Comments 4WKS] -= {'*'}> }[User Comments 4WKS])

[#4]

Gysbert,

I am sorry but no luck with suggested change in expression.

How can I replace a null with value in script?

[Gysbert_Wassenaar]

NullAsValue *;

SET NullValue='<NULL>';

If you add these two lines above your load statements then any nulls will be replaced with the string <NULL>. You can change that to something else in the SET NullValue statement.

[#6]

For example this is my load script:

First 10000

LOAD [AU ID],

     [First Name],

     [Last Name],

     [Subscriber Type],

     [Profile Country],

     [Profile City],

     [Email Open 12WKS Count],

     [Email Open 26WKS Count],

     [Email Sent 12WKS Count],

     [Email Sent 26WKS Count], 

     [Email Available],

     [Registered User]

FROM

(qvd);

First 10000

LOAD [AU ID],

     [User Comments 52WKS]

FROM

(qvd);

First 10000

LOAD [AU ID],

     [User Comments 26WKS]

FROM

(qvd);

First 10000

LOAD [AU ID],

     [User Comments 4WKS]

What changes I can make so it will take care of NULL Values?

Thanks,

TA

[#7]

Hi Gysbert,

I understood the first part to change the NULL Values. But can you please explain the second part "If you add these two lines above your load statements then any nulls will be replaced with the string <NULL>. You can change that to something else in the SET NullValue statement." or preferlabe worite SET expression accordinlgly.

Please reply.

Thanks,

TA

[#8]

Dear

1-in set analysis we use comma for or condition means you want to use multi Billing type

with the refrence of example1 you will have to use comma.

2- if you want to use Billing Type with Brand you have to use Follow Example 2

Example 1 : sum({<BillingType = {'ZDIS','ZDFR','ZMRS'}>} [BillingGross value])

Example 2 :  sum({<BillingType = {'ZDIS','ZDFR','ZMRS'},Brands{'abc,xyz'}>} [BillingGross value])

This is called or Condition or Multi set Analysis

[#9]

Hi,

Can I use value instead of a string for NULL Values?

e.g.: SETNullValue='0'; for example

[#10]

Hi Gysbert,

Let me explain you my problem. the you may be albe to figure it out in my attahced qvw.

I have three categories of Pages Viewed: in 52WKS, 4WKS and 1WK

It is possilbe in the data souce that for  certain AU_ID there is no pages viewed in Last 1WK and in Last 4WSK, but some pages viewed in last 52WKS. Now when I query this in the database it shows me NULL for 1WK and for 4WKS and pages viewed count for 52WKS.

I want same type of report in the form of a chart table as well. Pages viewed selections are shown on the left hand side from where I can select the sign and value for pages viewed in different weeks and on the right hand side you can see there are two chart tables one with IF and one with SET expression.

With IF expression there is no problem. Report shows me values 0 for both Last 1WK and for last 4WKS instead of NULL and show the page count for 52WKS. But SET expression omit this type of record at all. In other words if there is some SET expression does not convert NULL to 0 which is created because of the outer join.

I hope I am clear. If not please let me know. As you are always very helpful, the solution to this issue will highly appreciated. (May be challenge for you as well :-))

QVW is attached.

Thanks,

TA

PS: Are you based in USA or somewhere else?