You didn't include the datasource with your attachement so we can't open that workbook to check it out.
Do you need to use the Window_sum or will just a sum do? If you add the member identifier to the level of detail shelf and then use the sum([Bal S]) on the filter shelf, you should get your desired result?
Hi Nathan, thanks for your reply. I've now added the datasource (.tde file). I thought the actual datasource wasn't needed once extracted.
In any case, I've tried using just SUM and WINDOW_SUM but it still does not provide the expected results. I'm noticing that by adding the Member dimension (which is the identifier) to the level of detail, it causes the text to overlap in the summary report. However, as a general test, the concept does allow the filter to work properly, it just needs the report to look right.
Thanks for that. Not sure if you can do what you are asking... I had a play with using window functions on the other fields aswell because you have to have the member number in the level of detail to do the filter.
I only had a quick play and couldn't get the right results, but that would be the only way to get it I think.
I'm noticing that by adding the Member dimension (which is the identifier) to the level of detail, it causes the text to overlap in the summary report. However, as a general test, the concept does allow the filter to work properly, it just needs the report to look right.
Marellano, this might help with the overlapping marks:
You can find the whole discussion here: http://community.tableau.com/thread/111677
Also if you search the forums for FIRST()==0 you'll find a lot of discussion between Joe and Richard about this technique. Hope this helps.
Thanks for the suggestions Shawn and Nathan!
I will explore the FIRST()==0 option and report back on my results.
I've tried the recommend calculation:
IF FIRST()==0 THEN
The results came back the same as using just WINDOW_SUM(SUM([Bal S])). So either option seems to require, as Nathan pointed out, the Member dimension (which is the identifier) to be in the level of detail. However, the way the report is summarized, it causes the text to overlap, which I cannot have.
The only other alternative that I can think of is to create the aggregated calculation at the database level and use it as direct filter in the report.
Thanks for the report back. Sorry the news wasn't better.
I was going to suggest the database idea as a last resort. I've had to do that a few times myself.
I had one last idea you could try, I haven't had a chance to give this a go myself.....
What if you put the MemNum on the level of detail, then update your measures to be windowed functions.
Instead of using CountD(MemNum) to get the number of members, use Window_count(max([MemNum])). With the Memnum at level of detail, max([MemNum]) should be each [MemNum] therefore a window_count will return the distinct count. Your other measure could be done the same way?
As i said, didn't try it with your data, but I think this will work...
Hi Nathan, I sort of see where your heading with this. I'm getting some mixed results at the moment but let me play with the differnt levels for the calculation. Thanks for your assistance!!