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:

      test1.PNG

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

      捕获.PNG

      However, I wanna the result like this:

      test2.PNG

       

      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

                          Qianyu

                           

                          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

                            Prod

                          resident Data

                          ;

                           

                          Outer Join ( Temp )

                          load distinct

                            Hosp

                          resident Data

                          ;

                           

                          outer join ( Data )

                          load

                            *

                          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