5 Replies Latest reply: May 23, 2017 3:18 AM by Jon Argandona RSS

    How can I calculate co-ocurrence of sales using set analysis?

    Jon Argandona

      Hello,

      I have an app with two tables, one contains sales of objects and the other contains commercial information for those dates.

      I would like to obtain the co-ocurrence of sales for the objects (object 1 goes with 2). The measure I'm most interested on want to obtain is the number of sales when an object is sold,

      I think there must be a simple solution to this problem but, I'm quite newbie in Qlik and I'm struggling to do it.

      I attach an example with data.

      The result I would like to obtain is:

       

      OBJECT      CONCURRENTSALES     SALES

      1                    5                                        2

      2                    6                                        3

      3                    3                                        1

       

       

      Preferably I would like to be able to build the following matrix:

       

      OBJECT/OBJECT     1     2     3

      1                               2     2     1

      2                               2     3     1

      3                               1     1     1


      Any help is appreciated.


        • Re: How can I calculate co-ocurrence of sales using set analysis?
          Sunny Talwar

          Not entirely sure I understand the logic behind your expected output, would you be able to elaborate a little?

          • Re: How can I calculate co-ocurrence of sales using set analysis?
            Vineeth Pujari

            can you explain the logic for your desired output?

             

            OBJECT/OBJECT     1     2     3

            1                               2     2     1

            2                               2     3     1

            3                               1     1     1

              • Re: How can I calculate co-ocurrence of sales using set analysis?
                Jon Argandona

                Sorry, I uploaded an older version of the example. The load part should be this one:

                 

                T_Times:

                LOAD

                *

                Inline

                [

                DATE,OBJECT

                '2017-05-01',1

                '2017-05-01',2

                '2017-05-03',2

                '2017-05-04',1

                '2017-05-04',2

                '2017-05-04',3

                ];

                 

                 

                T_TimeInfo:

                LOAD

                *

                Inline

                [

                DATE,metric1

                '2017-05-01',17

                '2017-05-02',22

                '2017-05-03',23

                '2017-05-04',25

                ];

                 

                Each row in the table T_Times is a sale. I would like to count the sales happening at the same DATE an OBJECT is sold.

                For OBJECT=1, I would like to obtain dates '2017-05-01' and '2017-05-04', and using those dates I would like to (at least) count the number of OBJECTS sold. 

                If it is possible, I want to obtain the times two objects are sold together. My idea is to plot a heatmap or perhaps a barchart.


                OBJECT/OBJECT     1     2     3

                1                               2     2     1

                2                               2     3     1

                3                               1     1     1


                Thank you for your prompt response and sorry again for uploading the wrong example.

              • Re: How can I calculate co-ocurrence of sales using set analysis?
                Jon Argandona

                Hello,

                I tried your code but it is not what I want.

                I found a solution myself but I do not know if it is the best one: I made a copy of the table T_Times and renamed OBJECT as OBJECT2. However, I think it is not really efficient from a memoy point and I believe it can be troublesome in the long run. In any case it does the trick for this particular case. If I filter OBJECT=1, variable OBJECT2 will show me tho co-ocurrences on that date. Is there a way to do this by means of set analysis or should I use two variables?

                 

                T_Times:

                LOAD

                *

                Inline

                [

                DATE,OBJECT

                '2017-05-01',1

                '2017-05-01',2

                '2017-05-03',2

                '2017-05-04',1

                '2017-05-04',2

                '2017-05-04',3

                ];

                 

                T_Times2:

                LOAD

                *

                Inline

                [

                DATE,OBJECT2

                '2017-05-01',1

                '2017-05-01',2

                '2017-05-03',2

                '2017-05-04',1

                '2017-05-04',2

                '2017-05-04',3

                ];

                 

                 

                T_TimeInfo:

                LOAD

                *

                Inline

                [

                DATE,metric1

                '2017-05-01',17

                '2017-05-02',22

                '2017-05-03',23

                '2017-05-04',25

                ];