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!
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)
Thank you for your response, it is indeed in reference to your last post which I've amended to do the following:
If
(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.
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;
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.
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...
.png "Miguel_Angel_Baeyens"){kind=avatar}
