Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
MDX numeric functions 1
- Thread starter benaround
- Start date
- Thread starter
- #2
-
1
- #4
OK, I'll take a stab at trying to come up with what the original poster was looking for since I have spent the day pounding my head.
The fact table has a number of fields. Two dimensions:
CODE and DAY
Two measures:
AMT and QTY
I want to report total amount, total quantity, average amount, maximum amount, minimum amount.
I setup a measure called TOTAMT and use the aggregate function SUM. OK, got it. I setup a measure called MAXAMT and use the aggregate function MAX. Now this works but I'm not sure why. I think it works because of the same reason the SUM function works but I'm uncomfortable. I guess we are saying that the SUM, MAX, MIN, etc. values are calculated on the fly based upon the selection set? If so, how is this different from a calculated member?
Now the next complication is that I want MIN amount but only when the quantity is non-zero. That is, an amount of zero with a quantity of zero should not be included in determining the minimum - only those with a quantity > 0 should be considered.
Additionally, the average should be the sum of all the amounts divided by the sum of the quantities but a zero amount/quantity should not enter into the equation.
Are these two values calculated and how does one go about setting this up?
I looked at the calculated member stuff and wanted to try the MAX/MIN functions but it wanted two parameters versus the traditional one argument and I couldn't figure out what to put there.
I gotta get that book on MDX.
The fact table has a number of fields. Two dimensions:
CODE and DAY
Two measures:
AMT and QTY
I want to report total amount, total quantity, average amount, maximum amount, minimum amount.
I setup a measure called TOTAMT and use the aggregate function SUM. OK, got it. I setup a measure called MAXAMT and use the aggregate function MAX. Now this works but I'm not sure why. I think it works because of the same reason the SUM function works but I'm uncomfortable. I guess we are saying that the SUM, MAX, MIN, etc. values are calculated on the fly based upon the selection set? If so, how is this different from a calculated member?
Now the next complication is that I want MIN amount but only when the quantity is non-zero. That is, an amount of zero with a quantity of zero should not be included in determining the minimum - only those with a quantity > 0 should be considered.
Additionally, the average should be the sum of all the amounts divided by the sum of the quantities but a zero amount/quantity should not enter into the equation.
Are these two values calculated and how does one go about setting this up?
I looked at the calculated member stuff and wanted to try the MAX/MIN functions but it wanted two parameters versus the traditional one argument and I couldn't figure out what to put there.
I gotta get that book on MDX.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question