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

    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?


      Thanks in advance.

        • Re: Set Analysis Help
          yusuuf rawat

          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
                  Bill Markham

                  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
                          Bill Markham



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


                          a)    In line load of your test data in table Data

                          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 :


                          LOAD * INLINE [

                              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 :

                          load distinct


                          resident Data



                          Outer Join ( Temp )

                          load distinct


                          resident Data



                          outer join ( Data )



                          resident Temp



                          drop table Temp ;

                  • Re: Set Analysis Help



                    Try like this..

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

                    in scripting part