6 Replies Latest reply: May 23, 2015 9:39 AM by Bill Markham

# Set Analysis Help

For Example,

I have a table like this:

in the qv, when prod1 is selected, the result is like this below:

However, I wanna the result like this:

Could anybody help me write the expression?

• ###### Re: Set Analysis Help

Attached is the correct display, which is the correct way to show whether hosp a b ,c has a value or null .

• ###### Re: Set Analysis Help

Hi Dear,

Sorry to bother you again.

I have selected Suppress When Value Is Null and Show ALL Values for Hosp in Dimensions.

Bus I still only get one raw for prod1.

Is there anywhere else I need to set for achieving the result of yours?

• ###### Re: Set Analysis Help

How about something like the attached ?

• ###### Re: Set Analysis Help

Oh, It's amazing!

How do you make that?

I cannot find any difference in the chart dimension and expression setting.

could you help explain for me?

• ###### Re: Set Analysis Help

Qianyu

Have look at the load script, also pasted in below.  In essence :

b)    Load distinct values of Prod into table Temp

c)    Cartesian join of distinct values of Hosp into table Temp

• Table Temp now contains all combinations of Prod and Hosp

d)    Outer join table Temp onto table Temp

• Which fills in the missing Prod and Hosp combinations giving them a null() value for Value

Then once you have generated this table creating the Straight table is dead easy as you have observed.

I am an advocate of doing everything one can in the script so as to make the dashboard objects as easy to create and simple as possible.

Data :

Hosp, Prod, Value

a, prod1, 1

a, prod2, 2

a, prod3, 3

b, prod2, 4

b, prod3, 5

c, prod3, 11

c, prod3, 12

];

Temp :

Prod

resident Data

;

Outer Join ( Temp )

Hosp

resident Data

;

outer join ( Data )

*

resident Temp

;

drop table Temp ;

• ###### Re: Set Analysis Help

Hi,

Try like this..

if(IsNull(Value),0,Value) as Value

in scripting part

Thanks

Harsha