Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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.


5 Replies
sunny_talwar

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

vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

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.

vinieme12
Champion III
Champion III

try like below

T_Times:

LOAD

*,AutoNumber(Rowno(),OBJECT&DATE) as instancenumber

Inline

[

DATE,OBJECT

'2017-05-01',1

'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

];

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

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

];