Percentage of Sub-total

Lawrence Brown shared this problem 7 years ago
Completed

Hi,


Is it possible to use the 'Percentage of Total' function but calculated for Sub-totals?


8282b14f13d98e7b754af6d9c88cfb14


I have a report that calculates the % of the total number but I'd like it to do it for the Income and Expenditure respectively, I've included an additional column to show how I'd like the end result to look.


Regards,

Lawrence

Best Answer
photo

Hi Daryle,

Since there have now been a couple of requests for this functionality, I have put together a custom advanced function to achieve this.

Upload the attached jar file through the plugin manager. The new function will be called "Sectioned Percentage of Total". Apply this to the column of choice, and select the column used for your sub-totals as the reference column. In the original example you would select MA2 as the reference column.

857f812b031229e7e346a1ad47b277ef

Let me know if you run into any problems with this.

Regards,

Nathan

Replies (6)

photo
1

Hi Lawrence,


Thanks for sending in the question. This might not be the cleanest way to express this (there might be other ways to accomplish this via Report from Report), but I think to accomplish what you are after you are going to need to consider using sub queries.


Using basic append sub queries I was able to accomplish the following:


bb4aa290f595c2c5cb44c46ea8b8988a


In your case, the master query would contain your MA2, MA4, BUD, and % of Total columns. Then you could create an individual sub query for income and expenditure each (filtering the sub query by either income or expenditure to limit the sub query results) to produce an individual % of Total.


If you you haven't worked with sub queries before, our wiki has some great information on the subject:

http://wiki.yellowfin.com.au/display/USER72/Append+Sub+Query


Can you review this and let us know if this solution could work for you, or if you have any follow-up questions here? I look forward to hearing back!


Kind Regards,


Dustin

photo
1

Is this still the best way to calculate the % of subtotal?

photo
1

Hi Daryle,

Since there have now been a couple of requests for this functionality, I have put together a custom advanced function to achieve this.

Upload the attached jar file through the plugin manager. The new function will be called "Sectioned Percentage of Total". Apply this to the column of choice, and select the column used for your sub-totals as the reference column. In the original example you would select MA2 as the reference column.

857f812b031229e7e346a1ad47b277ef

Let me know if you run into any problems with this.

Regards,

Nathan

photo
1

I am going to set this ticket to closed for now, but if there is anything else I can do to help here, please just let me know and the case will be re-opened!


Regards,

Nathan

photo
1

Hi Nathan

I wanted to use your plug in but in my YellowFin installation (7.4.7), there is no Plugin Management link in the Administration section on the right of the Admin console (as per https://wiki.yellowfinbi.com/display/USER74/Install+Plug-in)

Is this a bug or is there a setting needed to enable plugins? My user has the admin role.


Thanks

Greg

photo
1

Hi Greg,

Chances are, your user is lacking the appropriate role permission for this. It should be "Plugin Management" under the administration section.

Nathan

photo
1

Thanks Nathan. I finally found the Plugin Management setting under the Administration setting and enabled it for the admin role.

Greg

photo
photo
1

Hi Nathan,

Could you attach the source code for the custom advanced function?. It woul be helpful to understand how to create one which receives the name of a column as a parameter (eg. the reference column in this case).

Good bye,

Gabriel

photo
1

Hi Gabriel,

I have attached the source code for this.

Nathan

photo
photo
1

Hi @Nathan

I hope you're doing well.


It's this function still working? I installed the plugin but i can't see the option on advanced function.


Looks like that doesn't work in crosstab, only in normal report. But if I do in normal report and after i change back is still working :D haha


Thanks

Evan

photo
1

Hi Evan,

This is only intended to work with non-crosstab reports. I think the following Idea might be what you'd be looking for here as well: Add a Percent of Total Row Totals Option.

Can you take a look at that Idea item and let me know if this appears to be what you're looking for? If so, please feel free to throw in a vote there and continue to follow the Idea item for any potential future updates.

Regards,

Mike

photo
1

Hi Mike,

Thank you for your answer.

Actually I used the plugin from Nathan in a report and I converted the report into crosstab and the function still works. I know that could be a bug but it worked for now.

I also already voted in this idea.

Thanks!

Best regards,

Evan Bessa

photo
1

Hi Evan,

Thanks for your response. So what you're saying is it works, you just have to toggle back to a normal report first to select it, but once you make it a crosstab, it still works, but it's just not found in the Advanced Function list when the report is a crosstab report? Is my understanding correct?

Regards,

Mike

photo
1

Hi Mike,

yes, this is really crazy, but the steps that i did were:

1. I converted the crosstab into normal table report

2. I used this advanced function into the column that I want and worked.

3. I converted back from normal report into crosstab

4. The advanced function still working, I can't edit this function but I can do any other change, so this is fine, at least while we have this bug :)

Thanks

Evan Bessa

photo
1

Hi Evan,

Thanks, I'm able to replicate this. I've gone ahead and logged a defect for this. That said, this is a custom JAR file, and there's a rather simple workaround, so I do find it likely this will be addressed, but it is a defect nonetheless so I will keep you posted on any progress made here in this ticket.

Regards,

Mike

photo
1

Hi Mike,

I understand, but it's not a problem for me since I can use in a crosstab, so if this stops to work. Please let me know and I can try to find another solution for that, as once the idea for the function is still in "idea ":)


Thanks

Evan

photo
1

Hi Evan,

Sure sounds good. Please follow the referenced Idea item for future updates there and I will keep you posted on this one here.

Regards,

Mike

photo
Leave a Comment
 
Attach a file