topic Re: count for each week in QlikView

count for each week

surajap123 — Fri, 06 Jun 2014 11:09:10 GMT

Hi All,

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

How can i achieve this in qlikview.

Re: count for each week

Fri, 06 Jun 2014 11:14:16 GMT

How is it 13?? Could u please explain it?

Re: count for each week

surajap123 — Fri, 06 Jun 2014 11:35:22 GMT

My requirement is to count the number of people occupying a Class Room in a particular week.

So here, people can start the course any time and end anytime, but i want to count number of students occupied that class room in a week.

So, in this example i got 13 because there are 13 people(IDs) are falling in the week 19/05/2014- 23/05/2014(highlited in bold)

Re: count for each week

Fri, 06 Jun 2014 17:51:52 GMT

Sorry, but l still don't understand how you got 13.

Thanks

Re: count for each week

Fri, 06 Jun 2014 18:03:03 GMT

So you don't care about how many unique ID's there are? Just how many total?

Re: count for each week

Fri, 06 Jun 2014 18:07:47 GMT

Also, how are you trying to display this? A pivot table? Straight table text box? Could you share a little more about the scenario, please?

Re: count for each week

Clever_Anjos — Fri, 06 Jun 2014 18:53:19 GMT

Please check if fits your needs

Re: Re: count for each week

surajap123 — Sun, 08 Jun 2014 14:52:14 GMT

Hi Clever,

Thanks a lot for the solution. This absolutely works.

Infact, I want to analyse the count based on 2 more dimensions. I tried to add those dimensions to pivot table and tried your script and expression, but it is not working. May be I need to do some changes to the expression or script.

Also, how can I link the week to master calendar, so that user can click on a particular week and can see the different Room counts for each dimension in pivot table?

Thanks a lot

Regards,

Suraj

Re: count for each week

Clever_Anjos — Sun, 08 Jun 2014 20:17:14 GMT

That solution works with a disconnected week field.

Could you post your qvw?

Re: count for each week

surajap123 — Sun, 08 Jun 2014 21:17:11 GMT

Hi Clever,

Thanks you so much !!

Yes,it is working with all my dimensions in the pivot table. Earlier i created wrong.

Just wondering how can i link this island table to the calender, so that when users click on the month and week number, they can able to see the count of students in each room for selection.

Also, what should be the number in Autogenerage function parameter for my requirement.