topic Count of Records Between two dates in QlikView

Count of Records Between two dates

Dayna — Tue, 08 Mar 2011 14:06:34 GMT

Hello All,

I have a table that shows the date for the record and the previous date (i.e. Date From - 01/01/2011 Date To - 10/01/2011) for everythign with 'Type A'. I would like to show on this row the number of records that fall between Date From and Date To. Now, the issue I have is I want to count all the records that are of Type B.

I'm using a simple Set Analysis expression to calculate my amount:

sum({$<tr_type = {'CYC-RCNT'}>} tr_qty_loc)

And to get the previous date I'm using this in the load script..

If(tr_part = Previous(tr_part), date(previous(tr_effdate)), 'N/A') AS tr_DateFrom,

I tried an IF statement (If greater than date and less than, then count (Field1)) but this doesn't work!

I hope you can help me!

Kind Regards,
Dayna

Count of Records Between two dates

Miguel_Angel_Baeyens — Tue, 08 Mar 2011 14:28:36 GMT

Hello Dayna,

If I understand correctly you want to know how many records are between one date and another. I'd do that in the script, according to my previous one, using something like

If(Product = Previous(Product), RangeSum(Peek('Count'), 1), 1) AS Count,

Then, you will need to use instead the Max(Count) per dimension (Product, in your example), since all records will have a value for "Count" (1, 2, 3, etc)

Hope that helps.

Count of Records Between two dates

Dayna — Tue, 08 Mar 2011 15:13:50 GMT

Hello Migual,

Thank you for your response, it is indeed in reference to your last post which I've amended to do the following:

(tr_part = Previous(tr_part) AND tr_type = 'CYC-RCNT', date(previous(tr_effdate)), 'N/A') AS tr_DateFrom,

Could you explain what you mean by the latter of your post? I've never really used RangeSum or Peek.. The field I'm trying to count is everything for tr_type = ISS-WO and the field is tr_lot.

Kind Regards,
Dayna

Count of Records Between two dates

Miguel_Angel_Baeyens — Tue, 08 Mar 2011 15:20:53 GMT

Hello Dayna,

Something like the following (not sure about your field names and so):

DataWithPreviousDate:LOAD Product, Date AS DateTo, If(tr_part = Previous(tr_part) AND tr_type = 'CYC-RCNT', Date(Previous(tr_effdate)), 'N/A') AS tr_DateFrom, If(tr_part = Previous(tr_part) AND tr_type = 'ISS-WO', RangeSum(Peek('IISWOCount'), 1), 1) AS IISWOCount, AmountRESIDENT OriginalDataORDER BY Product, Date;

Hope that makes sense.

Count of Records Between two dates

Dayna — Tue, 08 Mar 2011 16:06:34 GMT

Hello Miguel,

Where it's 'Count' in the expression, do I need to change this for the field I need to count - tr_lot?

I've tried pasting what you have above directly into my sheet but no joy!

Many thanks for your continued help,
Kind Regards,
Dayna

Count of Records Between two dates

Miguel_Angel_Baeyens — Tue, 08 Mar 2011 16:28:26 GMT

Again, my fault, I missed the fieldname there. Let's take a look at the new field we are creating:

If(tr_part = Previous(tr_part) AND tr_type = 'ISS-WO', RangeSum(Peek('ISSWOCount'), 1), 1) AS ISSWOCount

It will read "if the value in tr_part of the current record equals to the value for tr_part in the previous record, and tr-typr equals to 'ISS-WO', then add 1 to the last value in field ISSWOCount, otherwise set it to 1".

Peek() in this case returns the last value in a field ISSWOCount. RangeSum() just adds 1 to the current value of ISSWOCount for a given record.

It's not counting anything, actually, rather than creating a counter (as such will store from 1 to all possible values in the field). Assume that tr_part has 3 records where tr_type equals to 'ISS-WO'. ISSWOCount will have values 1, 2 and 3. Using Max(ISSWOCount) will return how many records are. Doing this in the script will save time and complexity in the expressions part in the charts.

I've attached a very simple example based on your post so you get the counter in this way for each product.

Hope that helps.

Count of Records Between two dates

Dayna — Tue, 08 Mar 2011 16:53:40 GMT

Hello Miguel,

Your very helpful, thank you for all your effort in the above.. I can see exactly what you mean, as it'll give me a count of ISS-WO per product.

For my next question, will the above be smart enough to give me a value for how many ISS-WO's there are between Data From and To for a CYC-RCNT?

Kind Regards,
Dayna

Count of Records Between two dates

Miguel_Angel_Baeyens — Tue, 08 Mar 2011 16:57:39 GMT

Hi,

It should be enough changing the field for the value you are actually looking for:

If(tr_part = Previous(tr_part) AND tr_type = 'CYC-RCNT', RangeSum(Peek('Count'), 1), 1) AS Count,

Is that correct?

Count of Records Between two dates

Dayna — Tue, 08 Mar 2011 17:20:56 GMT

I will certainly give it a go! Thanks again!

Count of Records Between two dates

Dayna — Wed, 09 Mar 2011 10:25:21 GMT

Hello Miguel,

The figures still don't work, I've attached what I currently have.. The best example is one of the last records, where the Date From and Date To for the CYC-RCNT's is 01/03 - 06/03. In the other table, I can see there's 6 ISS-WO entries between these dates...

I hope you can help!

Kind Regards,
Dayna

Count of Records Between two dates

Miguel_Angel_Baeyens — Wed, 09 Mar 2011 14:45:08 GMT

Hello Dayna,

Still don't understand where the issue is, because I don't know which values are correct and which are not., but I've seen that in the orange table you are not using the counter you just created in the script. So replace the expression "IISWOCount" for

Max(ISSWOCount)

Additionally, you can add the expression

Count({< tr_type = {'ISS-WO'} >} tr_lot)

that will return the number of values for "tr_lot" (not distinct) and see whether that starts to make sense.

Anyway, I'll keep doing that part in the script, but I'm not sure of the criteria. My guess is that you are looking for "count the number of tr_lot values between CYC-RCNT records". Following that RangeSum (or RangeCount) and Peek approach will do.

Regards.

Count of Records Between two dates

Dayna — Wed, 09 Mar 2011 15:06:18 GMT

Hey Miguel,

Yes, it's 'count the number of ISS-WO between the CYC-RCNT records', I had disabled the max(ISSWOCount) which brings the below.. But the value should be on the same row as the date from and to for the CYC-RCNT (Not show the ISS-WO records).

So really, the value for row 01/03 - 06/03 should be 4 according to the other table.

Hope this makes sense?

Many thanks,
Dayna

Count of Records Between two dates

Miguel_Angel_Baeyens — Wed, 09 Mar 2011 15:53:03 GMT

Hello Dayna,

As I cannot reload, I'll try to make some dummy data than can be helpful to what you are looking for.

Regards.

Count of Records Between two dates

Dayna — Wed, 09 Mar 2011 15:56:04 GMT

Hello Miguel,

Please find attached a QVD of that data, hope this helps!

Kind Regards,
Dayna