DuncanSutcliffe
Technical User
I have built a manual cross-tab and have a problem with certain summary values.
The logic is:
Formula 1: If Day = 1 Then [Value] Else 0 (this gives me the total value for day one)
Formula 2: If Day = 1 Then 1 Else 0 (sum this to get the count of values for day one)
To get a mean, I can just do Sum(Formula1)/Sum(Formula 2).
I now need a median, but it's difficult for the following reason. If there are one hundred rows in the source, but only twenty five of them belong to day one, I will have a one hundred results for Formula 1...but seventy five of them will be zero. So the median will always be zero. Even if ninety nine rows belong to day one, there will still be a single zero which will skew the result very slightly.
I somehow need to find out how many values really belong to day one, ignoring all zeros that have been generated by the formula - any ideas...?
Oh - I then need to calculate Std Dev as well.
TIA,
Duncan
The logic is:
Formula 1: If Day = 1 Then [Value] Else 0 (this gives me the total value for day one)
Formula 2: If Day = 1 Then 1 Else 0 (sum this to get the count of values for day one)
To get a mean, I can just do Sum(Formula1)/Sum(Formula 2).
I now need a median, but it's difficult for the following reason. If there are one hundred rows in the source, but only twenty five of them belong to day one, I will have a one hundred results for Formula 1...but seventy five of them will be zero. So the median will always be zero. Even if ninety nine rows belong to day one, there will still be a single zero which will skew the result very slightly.
I somehow need to find out how many values really belong to day one, ignoring all zeros that have been generated by the formula - any ideas...?
Oh - I then need to calculate Std Dev as well.
TIA,
Duncan