May 9, 2006 at 8:05 am
OK, this has been bugging myself and a colleague for two days now so I thought I'd throw it open and SQL Server Central sends me a lovely mail every day so I thought... why not here!
Question:
Given two products, product A and Product B (which is a recalculation of product A at a later time period), I need to find a sum of these over a period of time using the logic "IIF ([Product B] > 0, [Product B], [Product A])".
The problem is, if product A exists for five time periods, and product B exists for only 4 time periods, it evaluates the IIF statement over the whole time range - i.e. the cumulative value at period 5 is simply the 4 values for product B added together, because it wasn't null for the whole date range. What I would really want is 4 instances of product B + one value for Product A.
Is there any way to force a calculation such as the above to work out the values for the lowest level of the time dimension and roll up ? Or is there a simpler method ?
Cheers for any help anyone can offer.
Mike
May 10, 2006 at 9:53 am
I've got no idea what you are trying to do. Could you re-state your question? Perhaps give an example of the situation. Grr indeed.
May 11, 2006 at 6:39 am
Does product refers to a numeric value (sales, price e.g.) ?
If it does not, I don't understand what you are trying to compare.
If it does then: What you want to compare is each instance of product B with the last instance of product A (or the average of product A)?
As far as the time dimension is concerned, I can't understand what you are asking for.
Does product A and product B exist simultaneously or product A exist until a certain period and then product B replaces it?
May 11, 2006 at 3:25 pm
Apologies... it was my colleagues question to me which had me stumped and I essentially just pasted in his question without illustrating it.
Ironically... whilst typing out an explanation just now I think I might have cracked it, but unfortunately I'm at home on the laptop - unable to test so I'll try when I get back in to work tomorrow... and if I don't get anywhere I'll post a more illustrative example of what we are trying to do!
Cheers for the responses,
Mike
May 12, 2006 at 4:53 am
Solved it. Colleague cheated and changed the FACT table to support the requirement instead.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply