topic Re: find value based each week in QlikView

find value based each week

surajap123 — Tue, 27 May 2014 12:38:16 GMT

Hi All,

I have 3 columns in a table.

Sales StartDate EndDate

12 19/05/2014 25/07/2014

3 14/04/2014 20/06/2014

5 28/04/2014 27/06/2014

9 28/04/2014 27/06/2014

15 28/04/2014 01/08/2014

8 19/05/2014 27/06/2014

I want to know number of sales happend in each calender week. So when user select a week they could able to see number of sales happened on that particular week.

To allow the user to select a week, i dont have any week field. So i think i neeed to create a master calender.

But,Since the table has StartDate and EndDate, I am not sure how can i link these 2 dates to create a calender?

Please suggest !!

Re: find value based each week

tresB — Tue, 27 May 2014 12:40:10 GMT

Have a look at intervalmatch(), that should help you.

Re: find value based each week

surajap123 — Tue, 27 May 2014 14:43:56 GMT

Hi tresesco,

Thanks a lot for the suggestion..

I have gone through the intervalmatch function and tried to apply it for my requirement. But i am stuck while writing the script for my requirement. May be because i never used this before.

My requirement is to count the candidates occupied the room PER WEEK.

For each PersonID, i have From and ToDate. But dont have another single date which i can use in intervalmatch.

How can i use intervalmatch in this scenario?

Please have a look at the attached app i developed for my requirement.

Please suggest the script..

Re: find value based each week

surajap123 — Wed, 28 May 2014 09:23:57 GMT

Hi All,

Could anyone help me on this..I tried a lot, but couldnt get the right output..

Re: find value based each week

robert_mika — Wed, 28 May 2014 09:34:39 GMT

Add column Week Number in your ERoom data sheet and add:

=WEEKNUM(C2)

Re: find value based each week

surajap123 — Wed, 28 May 2014 09:59:44 GMT

Hi Robert,

Thanks for your reply.

If you look at ERoom Sheet, the Date range(FromDate and ToDate in the ERoom Sheet) is more than a week. In some row, the week range can be 3 weeks and different in other row.

So, how can I add one Week Number for each row, in the sheet?

Re: find value based each week

surajap123 — Wed, 28 May 2014 10:13:13 GMT

Hi Robert,

To simplify, I want to count the IDs which fall in a week range.

In this example the week is 19/05/2014 - 23/05/2014. So here the answer is 13.

FromDate	ToDate	ID
19/05/2014	25/07/2014	12
14/04/2014	20/06/2014	24
28/04/2014	27/06/2014	19
28/04/2014	27/06/2014	12
28/04/2014	01/08/2014	23
19/05/2014	27/06/2014	9
19/05/2014	27/06/2014	9
31/03/2014	06/06/2014	2
31/03/2014	23/05/2014	25
28/04/2014	13/06/2014	16
28/04/2014	23/05/2014	27
12/05/2014	13/06/2014	16
12/05/2014	23/05/2014	15