Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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

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

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

vinieme12
Esteemed Contributor II

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

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

Not applicable

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

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
Esteemed Contributor II

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

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

];

Not applicable

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

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

];

Community Browser