Announcements
cancel
Showing results for
Did you mean:
Not applicable

Shall I use InMonth function?

Hi All,

I have the following 2 tables:

Usage:
[
MemberId, Category, CompletionDate
1,IPhone4,2010/02/02
];

RegisterTB:
[
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.

1 Solution

Accepted Solutions
Employee

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.

7 Replies
Employee

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.

Not applicable
Author

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.

Employee

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.

Not applicable
Author

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.

Employee

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.

Not applicable
Author

OK, thanks, teacher.

Employee

`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!

Community Browser