count for each week

[surajap123]

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.

[#2]

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

[surajap123]

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)

[#4]

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

Thanks

[#5]

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

[#6]

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?

[Clever_Anjos · Accepted solution]

Please check if fits your needs

[surajap123]

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

[Clever_Anjos]

That solution works with a disconnected week field.

Could you post your qvw?

[surajap123]

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.