I'm trying to draw a chart which shows one groups sales figures against the sum of all groups sales figures within a database, over quarters. I also want to index those figures, basically to show how one is doing against the sum of the whole in terms of growth, rather than absolute terms.
However, for a particular group, I can't guarantee that I'll have sales figures for them in each quarter. So for example, if I'm showing data for 10 group, 9 of them might have data for Q1 in 2008, but the 10th doesn't have that figure.
At the moment I'm using the following to give me the expression:
So as you might already have figured out, if I'm charting the sales movements for 2008, and all the groups have data for 2008 Q1 except for one of them, the line for that particular group doesn't show. That is because the "top" value in this case doesn't exist, and therefore it doesn't know what to divide by.
So what I'm looking for is a way to find not necessarily the "top" value of the underlying table, but the "top AND populated" value. In that case, the rest of the groups would be able to use their 2008 Q1 value as their starting point, while the 10th company can use 2008 Q2.
