So this problem has been a real head-scratcher for me. I've been getting into set analysis over the past few days, and this forum has been a huge help overcoming hurdles in this process, but I'm quite stumped on this one.
I've been trying to exclude orders that have certain types of extensions marked from being averaged in a duration. When I thought this count defaulted to 0, this was working fine, but as it turns out I need to account for nulls as well, so I came up with something that looks like the following:
NoExtTAT:
SUM({$<[KeyDate]={">=$(=TimeStamp($(vFromDate)))<=$(=TimeStamp(DayEnd($(vToDate))))"}>
*<[active]={1}>
*<[nonNullField]={'*'}>
*($-<[extension1]={">0"}>)
*($-<[extension2]={">0"}>)
}
[duration])
/
COUNT({$<[KeyDate]={">=$(=TimeStamp($(vFromDate)))<=$(=TimeStamp(DayEnd($(vToDate))))"}>
*<[active]={1}>
*<[nonNullField]={'*'}>
*($-<[extension1]={">0"}>)
*($-<[extension2]={">0"}>)
}
DISTINCT [orderid])
Now, this works as expected as a full aggregation, and as an aggregation along many different dimensions, but there are some dimensions in my data that are, well ... disconnected from this set, seemingly. When this expression goes on a table with these dimensions, the calculation will duplicate lines as follows:
Normal:
| orderid | active | extension1 | extension2 | NoExtTAT |
|---|
| 1 | 1 | 0 | 0 | 5.2 |
| 2 | 1 | 1 | - | - |
| 3 | 1 | - | - | 3 |
With Problem Dimension:
| orderid | areatype (problem) | active | extension1 | extension2 | NoExtTAT |
|---|
| 1 | Urban | 1 | 0 | 0 | - |
| 1 | - | - | - | - | 5.2 |
| 2 | Rural | 1 | 1 | - | - |
| 2 | - | - | - | - | - |
| 3 | Urban | 1 | - | - | - |
| 3 | - | - | - | - | 3 |
This is driving me crazy - based on the construction of the set, NoExtTAT should evaluate as null on the rows it appears on - and furthermore it appears to be ignoring some tables in the dataset. areatypeid doesn't cause it to split like this, but areatype name does - it's like it can't 'see' the dim table for areatype (only 3 rows long).
Extension1, Extension2, active and nonNullField lie in the same table while areatypeid lies in a table joined to this by orderid and areatype is in a dim table joined from that. It should be noted that the following without the extension exclusions works fine at all levels I've viewed it at:
allTAT
SUM({$<[KeyDate]={">=$(=TimeStamp($(vFromDate)))<=$(=TimeStamp(DayEnd($(vToDate))))"}>
*<[active]={1}>
*<[nonNullField]={'*'}>
}
[duration])
/
COUNT({$<[KeyDate]={">=$(=TimeStamp($(vFromDate)))<=$(=TimeStamp(DayEnd($(vToDate))))"}>
*<[active]={1}>
*<[nonNullField]={'*'}>
}
DISTINCT [orderid])
