topic Shall I use InMonth function? in QlikView

Shall I use InMonth function?

Mon, 28 Mar 2011 13:06:56 GMT

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.

Shall I use InMonth function?

Miguel_Angel_Baeyens — Mon, 28 Mar 2011 13:25:25 GMT

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.

Shall I use InMonth function?

Mon, 28 Mar 2011 14:14:19 GMT

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.

Shall I use InMonth function?

Miguel_Angel_Baeyens — Mon, 28 Mar 2011 14:41:54 GMT

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.

Shall I use InMonth function?

Mon, 28 Mar 2011 15:56:53 GMT

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.

Shall I use InMonth function?

Miguel_Angel_Baeyens — Tue, 29 Mar 2011 07:42:24 GMT

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.

Shall I use InMonth function?

Tue, 29 Mar 2011 08:49:06 GMT

OK, thanks, teacher.

Shall I use InMonth function?

Miguel_Angel_Baeyens — Tue, 29 Mar 2011 08:59:58 GMT


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!