Reverse Running Sums and Set Aliases (by Richard Tkachuk)
At the 2007 BI Conference (great event, btw – had a blast), I was asked the question “How would you compute the number of days before the current period that someone spent 20% of the value they had spent until that day.”
I gave, I thought at the time, a reasonable answer. I suggested doing a nonempty on the leaves of the time hierarchy, computing the running sum for each day from the beginning of time until the current day (or the last day in the current period if the current member wasn’t at the day level), filtering the result so that the running sum exceeded 20% of the total and then computing the number of days between the first day in the filtered result and the current day.
Then I returned to my office and tried it myself – it was hard. If you’re an MDX expert, go try it yourself before you read on. I learned a new trick while doing it that might help me other unrelated problems.
Tags: mdx