12 Replies Latest reply: Feb 18, 2013 2:00 PM by Felipe Carrera RSS

    IF and set analysis

    Felipe Carrera

      hi

      somebody knows how this IF-statement would look like in set analysis?

       

      if(x= 'ab' and y='cd', 0, count distinct(z))

      thanks for the help

        • Re: IF and set analysis
          Martin FAVIER

          Hi,

           

          Your expression is wrong.

          It can be

          if(x='ab' and y='cd', 0, Count(Distinct z))

          or

          Count(Distinct if(x='ab' and y='cd', z))

           

          Only the 2nd may be translated in Set Analysis :

          Count({<x={ab}, y={cd}>} Distinct z)

           

          Hope that helps you

           

          Martin Favier

            • Re: IF and set analysis
              Felipe Carrera

              hi Martin

              thanks. I tried already that way. it works, but i want to set a condition to the intersection of x and y (two dimensions), where the values are "ab" AND "cd".

               

              other values of the dimension are not showed if i use the set analysis, but i want to see everything. Like this:

              the dimensions are X, Y and are assigned different values. I want that when X=ab AND Y = cd, then it should show zero, otherwise, it should count the rest and show them in the fields

               

              Header 1Header 2Header 3Header 4
              Y = aY=c
              Y=cd
              X = e451212
              X = u789874
              X= ab121445

               

              Final result shall look like

              Header 1Header 2Header 3Header 4
              Y = aY=c
              Y=cd
              X = e451212
              X = u789874
              X= ab12140
                • Re: IF and set analysis
                  Martin FAVIER

                  Ok I understand,

                   

                  so it's a little bit more complicated.

                   

                  Have you tried this (if we consider ID as the Primary Key) ?

                   

                  Count({<ID=E({<x={ab}, y={cd}>} ID)>} Distinct z)

                   

                  This expression depends on your datamodel.

                   

                  Hope that helps you

                   

                  Martin Favier

                  • Re: IF and set analysis
                    Martin FAVIER

                    Ok I understand,

                     

                    so it's a little bit more complicated.

                     

                    Have you tried this (if we consider ID as the Primary Key) ?

                     

                    Count({<ID=E({<x={ab}, y={cd}>} ID)>} Distinct z)

                     

                    This expression depends on your datamodel.

                     

                    Hope that helps you

                     

                    Martin Favier

                    • Re: IF and set analysis
                      Martin FAVIER

                      Ok I understand,

                       

                      so it's a little bit more complicated.

                       

                      Have you tried this (if we consider ID as the Primary Key) ?

                       

                      Count({<ID=E({<x={ab}, y={cd}>} ID)>} Distinct z)

                       

                      This expression depends on your datamodel.

                       

                      Hope that helps you

                       

                      Martin Favier

                        • Re: IF and set analysis
                          Felipe Carrera

                          thanks

                          i tried, but the result is nothing (the formula shows no errors, but i do not get any number, i thinking i am making a logical error)

                          my primary key in the data structure is contact_id, which is at the same time the field i am counting

                          i imagine with this formula, i am counting the total number of customers, excluding the customers which fall into the intersection of segments:

                           

                          Count({$<contact_id = e({<[Segment_1] ={'NewCustomer'},[Segment_2]={'LostCustomer'}>} contact_id) DISTINCT contact_id)

                           

                          Segment_1 = X

                          Segment_2 = Y

                           

                          and

                           

                          'NewCustomer' = 'ab'

                          'LostCustomer' = 'cd'

                           

                          the contact_id would be ID and Z

                           

                          what is wrong?

                           

                            • Re: IF and set analysis
                              Martin FAVIER

                              Are all your contact_id in NewCustomer AND LostCustomer ?

                               

                              Martin Favier

                                • Re: IF and set analysis
                                  Felipe Carrera

                                  no, they are distributed everywhere. this a customer migration table, so it is important to see how many customers migrated from a segment_1 in a previous period to a segment_2 in the next period. It may be that they stayed in the segment, and this will be reflected in the diagonal. If they migrated, either up or down in the segment, this will be reflected in the other fields.

                                   

                                  If there are nulls, this is reflected in the new / lost customers row / column, and finally, i have the NULL / NULL combination, this is, when the customers are located in the intersection NewCustomer AND LostCustomer.

                                   

                                  I got this solved, but i made an outer join of a duplicated table, with different names (for period and segment). then included an IF statment in the script to the the "NULL / NULL" combination and flag it. Then in the expression i used set analysis on only one field (the flagged one) and it worked.

                                   

                                  the problem is, i have now more than 4 billion records and the performance is bad. Aggregating data previously in SQL, last as well and seems not to be better., so i thought there may be a way with set analysis directly in the chart

                                   

                                  hopefully it is clear, thanks for your help

                                  felipe

                                • Re: IF and set analysis
                                  Martin FAVIER

                                  Are all your contact_id in NewCustomer AND LostCustomer ?

                                   

                                  Martin Favier

                                  • Re: IF and set analysis
                                    Martin FAVIER

                                    Are all your contact_id in NewCustomer AND LostCustomer ?

                                     

                                    Martin Favier

                            • Re: IF and set analysis
                              Khadar basha shaik

                              Hi,

                               

                              Can you please provide test data in excel,what ever formate you are getting.

                               

                               

                               

                               

                              Regards

                              h

                                • Re: IF and set analysis
                                  Felipe Carrera
                                  hi
                                  the table structure is like this:
                                  table:
                                  LOADcontact_id,
                                  month,
                                  s
                                  egment;
                                  SQL SELECT *
                                  FROM DATABASE;
                                  i duplicated this table like (previously store as table.qvd):
                                  table1:
                                  LOADcontact_id,
                                  month as month_1,
                                  s
                                  egment as segment_1
                                  FROM table.qvd (qvd);
                                  table2:
                                  LOADcontact_id,
                                  month as month_2,
                                  s
                                  egment as segment_2
                                  FROM table.qvd (qvd);
                                  i got an extra "contact" table including only my customers and their countries
                                  I attached the excel sheet with the data structure