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!
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.
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.
.png "Miguel_Angel_Baeyens"){kind=avatar}
