Shall I use InMonth function?

[#1]

Hi All,

I have the following 2 tables:

Usage:
load * Inline
[
MemberId, Category, CompletionDate
1,IPad,2010/01/02
1,IPhone4,2010/02/02
];

RegisterTB:
LOAD * Inline
[
Month,MemberId
2010/01/01,1
]

The first is the orders that customer bought something, the second is the register table that is used to show when the customer became our member.

Now I want to use pivot table to show how many product the member has bought during the registeration month.

In this test data, the results shoud be:

MemberId Month Quantity

1 2010/01/01 1

How to calculate that Quantity?

Thanks.

[Miguel_Angel_Baeyens · Accepted solution]

Hello Isaac,

If the dimension es Month from RegisterTB, I'd use the MonthStart and MonthEnd functions:

Count({< CompletionDate = {'>=$(=MonthStart(Month))<=$(=MonthEnd(Month))'} >} MemberId)

Think of creating a master calendar. There are a lot of good posts and an entry in the Wiki on how to get this done.

Hope that helps.

[#3]

Hi teacher,

Another additional question, if I want to calculate how many people have bought at least 2 products during the registeration, how to handle it?

In this sample , the results look like this:

Month Count

2010/01/01 1

How to modify that set analysis?

Thanks.

[Miguel_Angel_Baeyens]

Hello Isaac,

You'd need to add a count something like

Count({< CompletionDate = {'>=$(=MonthStart(Month))<=$(=MonthEnd(Month))'}, MemberId = {"=Count({< CompletionDate = {'>=$(=MonthStart(Month))<=$(=MonthEnd(Month))'} MemberId) >} > 1"} >} MemberId)

Hope that helps.

[#5]

Hi teacher,

why do we need to add completiondate to restrict the expression?

If I remove it, and use this directly:

Count({< MemberId = {"=Count({< CompletionDate = {'>=$(=MonthStart(Month))<=$(=MonthEnd(Month))'} MemberId) >} > 1"} >} MemberId)

Is there any difference?

Thanks.

[Miguel_Angel_Baeyens]

Hello Isaac,

I sometimes to do, even for the sake of clarity. The expression without CompletionDate at the beginning as you post should return proper results, since you are already filtering those MemberId that have already bought a certain number of times in that period of time.

Regards.

[#7]

OK, thanks, teacher.

[Miguel_Angel_Baeyens]

Isaac wrote:OK, thanks, teacher.

You're welcome. I'm glad to help.

But there are a lot of people here in the Community with huge knowledge about QlikView in particular and BI or DB subjects in general from whom I've learned a lot in turn. I don't see myself as teacher, and certainly not speaking about QlikView!