Market Basket Analysis in QV

[#1]

Hi guys,

I have a datasource like below, and want to have a basket analysis report,

TransId	Item	Amt
P1	A	1
P1	B	1
P2	A	1
P2	B	1
P2	C	1
P3	A	1

What i need to show in QV is as below which means how many times the items appears by TransId, for example item A appears in all of the TransId, item A+B only appears in P1 and P2, while A+B+C only appears in P2.

Item	Times
A	3
A+B	2
A+B+C	1

Maybe can use P( ) function?

Can anyone help? Thanks a lot.

[Gysbert_Wassenaar · Accepted solution]

Yes, you can use the P function. See attached qvw.

[#3]

Thank you very much.

Another question is whether the dimension can calculated dynamically?

I mean we don't know the possible combination of the items, maybe the dataset didn't have TransId of P3, in this case only have 'A+B' and 'A+B+C' for the dimension.

[Gysbert_Wassenaar]

There is no dimension in the example I posted. There are only expressions.

[#5]

Yes, understand.

But i'm afriad you don't got my point, I want to konw if it is possible to using the calculated dimension to do this? Because in the actual scenario, we don't know the combination of the items, if the datasource is as below, then it may not work, because the A should not be shown.

Thank a lot.

Datasource:

P1	A	1
P1	B	1
P2	A	1
P2	B	1
P2	C	1

Result:

A+B	2
A+B+C	1

[Gysbert_Wassenaar]

I don't understand what you want to use a calculated dimension for. What values should be calculated as dimension values? And why can't these values be calculated in the script?

[#7]

Thanks, got it and i will calculate it in scripts.

[senarath]

Hi Gysbert,

Sorry for answering a closed incident. But I need to learn P function and why you have used set analysis that way. I mean using something like >*<, is it how p function works OR something used in set analysis ?

Thanks

Senarath

[Gysbert_Wassenaar]

The * is one of the four operators you can use to combine sets. The * is used to calculate the intersection of two sets. See this document: Set Analysis Presentation.pdf