Rollup for percentage in a visualization chart

[farlein]

Hi there,

I have a problem with rolling up for percentage in a visualization chart. The visualization is called Stacked Column with Legend using Dynamic color scheme. My measure is retention rate, the category is Campus, and I have two fields for color, Residency and Gender. Campus, Residency and Gender are controlled by three data drop down lists.

By default, the visualization will only show Campus on X-axis and stack Residency, so the retention rate needs to be rolled up for gender. For example, the overall retention rate is 78%, the retention rate for female is 81%, and the retention rate for male is 77%. The visualization shows correct results if a specific gender is chosen, but if gender is not selected, (81%+77%=)158% will be shown rather then 78%. I attached incorrect results. Can anyone help me get out of this?

Thanks in advance!

[farlein]

It seems nobody wants to say something about it... Do I not state my problem clearly, or is it too dumb?

[MFGF]

It seems nobody wants to say something about it... Do I not state my problem clearly, or is it too dumb?

Nobody wants to say something about it? You make it sound like it's a deliberate ploy to keep you in the dark. Do you really assume that people know the answer and are unwilling to give it to you? Really?

This forum is a community where members help each other where they can (and if they can). It operates on a goodwill basis - nobody is paid or recompensed in any way for the assistance and contributions they give to others. Almost everyone on here has a full-time job of their own, and are probably under at least as much pressure as you to get things done. This isn't a place you can come to and demand instant answers - IBM offer a support service that you can use, and they have SLAs which dictate they must respond to you in a certain time. You pay for that privilege, though.

If anyone here has anything helpful for you, they will post it when they get chance. Pushing for answers like this serves only to deter potential responders - they are freely giving help and advice - in their own time and out of the goodness of their hearts, so why would they feel inclined to take time to do that for a forum member who appears pushy and demanding?

Your question isn't dumb - it appears quite involved, in fact. I'd suggest someone would need to try to recreate it locally to see what is going on - which takes time and effort on their part. Maybe they are busy doing their real jobs, to earn money to support their families? Maybe it's something they might have to do in evenings or weekends (if they feel so inclined). Maybe your being pushy for an answer would make them think twice about whether they would want to spend time helping you at all?

In the meantime, while you wait for an answer, what else have you tried? Are you just sitting there idly waiting for someone to magically serve up a solution, or are you busy trying alternatives of your own? If so, what have you tried? What results did you get? How much effort of your own are you putting in to solve this?

MF.

[farlein]

Sorry to make you upset, MF. My key points for my last post are I want to make sure I have stated my problem clearly and the question is not too simple. But I do apologize for making people think I am just demanding answers and not considering their feelings.

I have tried more than one week to solve this problem. I read the IBM cook book and the chapter in Report Studio user's guide for active reports. I also tried to find my answer in all 13 pages for active report here before my post. I find you helped people a lot in previous posts, so I know you do not just to come here to question me.

A simple solution for this specific case is putting the visualization in a data deck and make the default card visible. In the default card I can put a visualization without Gender, and when a client chooses a gender, the data deck should show the other card with a visualization with Gender. However, this solution can work only for one extra category, based on my understanding. I actually have six extra categories for color, so I will be crazy if I need to create separate cards for each combination of selection (e.g., only choose gender, or only choose ethnicity, choose gender and ethnicity but leave others unselected, or or only choose gender and another category, or choose three of the six).

I guess the nature of this problem is how active reports and/or visualization handle rollup method. I did not verify this, so this is just my guess. The visualization stores a crosstab, and the measure is rolled up based on the data in the crosstab rather than calculating from the original query. So I was wondering whether Visualization Customizer can help, but then I found this is very useful to modify style and format, but maybe not a solution to this problem. Maybe it can solve this problem, but I did not investigate it enough. Now I am trying to get a method to create a separate crosstab which rolls up measure from the original query and then pass its data to the visualization, but I do not know how to deal with this either...

[MFGF]

Sorry to make you upset, MF. My key points for my last post are I want to make sure I have stated my problem clearly and the question is not too simple. But I do apologize for making people think I am just demanding answers and not considering their feelings.

I have tried more than one week to solve this problem. I read the IBM cook book and the chapter in Report Studio user's guide for active reports. I also tried to find my answer in all 13 pages for active report here before my post. I find you helped people a lot in previous posts, so I know you do not just to come here to question me.

A simple solution for this specific case is putting the visualization in a data deck and make the default card visible. In the default card I can put a visualization without Gender, and when a client chooses a gender, the data deck should show the other card with a visualization with Gender. However, this solution can work only for one extra category, based on my understanding. I actually have six extra categories for color, so I will be crazy if I need to create separate cards for each combination of selection (e.g., only choose gender, or only choose ethnicity, choose gender and ethnicity but leave others unselected, or or only choose gender and another category, or choose three of the six).

I guess the nature of this problem is how active reports and/or visualization handle rollup method. I did not verify this, so this is just my guess. The visualization stores a crosstab, and the measure is rolled up based on the data in the crosstab rather than calculating from the original query. So I was wondering whether Visualization Customizer can help, but then I found this is very useful to modify style and format, but maybe not a solution to this problem. Maybe it can solve this problem, but I did not investigate it enough. Now I am trying to get a method to create a separate crosstab which rolls up measure from the original query and then pass its data to the visualization, but I do not know how to deal with this either...

What Aggregate Function property is defined for your measure (retention rate)? I'm assuming total? Is it as easy as switching it to be Average?

MF.

[farlein]

Thank you for your reply, MF. I tried it, but this method does not work if we have different numbers of male and female in the first fall. For example, here are some makeup data:

                 1st Fall   2nd Fall  Retention Rate
Female        100          81          81%
Male            1000        780        78%

Average of retention rate = (81%+78%)/2=79.5%
Actual retention rate for all = (81+780)/(100+1000)=78.2%

So the average and the actual number are close, but not the same.

[MFGF]

Thank you for your reply, MF. I tried it, but this method does not work if we have different numbers of male and female in the first fall. For example, here are some makeup data:

                 1st Fall   2nd Fall  Retention Rate
Female        100          81          81%
Male            1000        780        78%

Average of retention rate = (81%+78%)/2=79.5%
Actual retention rate for all = (81+780)/(100+1000)=78.2%

So the average and the actual number are close, but not the same.

Hmmm. So are you saying Retention Rate is defined as a calculation in your report? If so, what is your expression?

MF.

[farlein]

The expression for retention rate is (2nd Fall) / (1st Fall) *100, and it is calculated in the query for the visualization.

The data container for the visualization is like this:

Size:
   Retention Rate

Columns:
   Campus

Colors:
   Residency
   Extra categories:
      Gender

I also tried to put 1st Fall and 2nd Fall in the Extra values for size and hoped the retention rate would be calculated from them, but it is not. The retention rate is still rolled up from the field in the crosstab stored in the visualization.

[MFGF]

The expression for retention rate is (2nd Fall) / (1st Fall) *100, and it is calculated in the query for the visualization.

The data container for the visualization is like this:

Size:
   Retention Rate

Columns:
   Campus

Colors:
   Residency
   Extra categories:
      Gender

I also tried to put 1st Fall and 2nd Fall in the Extra values for size and hoped the retention rate would be calculated from them, but it is not. The retention rate is still rolled up from the field in the crosstab stored in the visualization.

So how about changing it to be

total([2nd fall] for report) / total(1st fall] for report) * 100

MF.

[farlein]

It is an interesting idea, but it does not work for this problem. The retention rate is still around 150% when gender is not selected. Inspiring by this, I tried
total([FFR_ENROLLED_FALL_2] for [CAMPUS],[CURRENT_RESIDENCY],[GENDER])/total([FFR_ENROLLED_FALL_1] for [CAMPUS],[CURRENT_RESIDENCY],[GENDER]),
and this does not work as well...

Thank you very much for your thoughts, MF! I will continue to work on this, and I will post my findings if a solution is found. Please also keep me posted if you find anything.

Happy Holiday!

[bladeless]

Have anybody found a solution for this issue?

[thebestschen]

Same problem here:( Has anybody found a solution in the meantime?

[pchandra]

Has anybody solution for this issue?