How Do show my field values in % using the summery value total?

[gamma]

Hi,

I am still new to A5 and maybe the way to solve my problem is so easy, that nobody else got an answer to this in the forum or at least I could not find it.

Following situation:

I have got a grid for a table with the summary value "Total " switched on looking something like this:

Name A B C SUM
________________________
first | 1 | 3 | 2 | 6
---------------------------
second| 2 | 1 | 2 | 5
---------------------------
Third | 2 | 3 | 2 | 7
________________________
Total | 5 | 7 | 6 | 18


I want to show the table in percent of each field value divided by the total value:


Name A B C SUM
___________________________________
first | 20% | 43% | 33% | 33%
----------------------------------------
second| 40% | 14% | 33% | 28/
----------------------------------------
Third | 40% | 43% | 33% | 39%
__________________________________
Total | 100% | 100% |100% | 100%


1. How can I calculate with the "Total" Summary field
2. Where do I enter the calculation? In the Display format field?


Thank you very much for your help!

[Keith Hubert]

Re: How Do show my field values in % using the summery value total?

Hi Grainne,

Welcome to Alpha.

Please see attached thumbnail. This is where you create your Calculated field, which can then be displayed in the grid.

[gamma]

Re: How Do show my field values in % using the summery value total?

Hi Keith,

Thank you for That fast reply with screenshot and all. I am quite impressed. I did not think anybody would reply so fast.

I forgot to mention that I am using a remote database accessed via AlphaDAO and then there is no Calculated Field button. But know I know where to find it using DBF.
Thank you! I searched a bit more and found in the SQL Builder the possibility to enter an expression. But here it seems I can only calculate in one row and not as needed over a column. Is there a way to get the data from the "Total" summary field?

A5_totalSummaryField.JPG

compared with Excel I would need following

A1 B1 C1
A2 B2 C2
A3 B3 C3

A1/Sum(A1:A3) *100 %
A2/Sum(A1:A3) *100 %
A3/Sum(A1:A3) *100 %


and so on.
And that seems not to work with the row wise expression.
Thank you!

[Keith Hubert]

Re: How Do show my field values in % using the summery value total?

Sorry, I cant help with SQL at the moment.

Will keep watching and hope to learn too.

[gamma]

Re: How Do show my field values in % using the summery value total?

Hi again,

as soon as I know I will Post it here.
Have a great weekend!

[Jim Coltz]

Re: How Do show my field values in % using the summery value total?

Thank you! I searched a bit more and found in the SQL Builder the possibility to enter an expression. But here it seems I can only calculate in one row and not as needed over a column. Is there a way to get the data from the "Total" summary field?

You can also calculate over the column by using an aggregate function like SELECT SUM(extension) As Total_Extension FROM invoice_items WHERE INV_NUMBER = 1; (MySql example). For a list of the A5 portability functions to use see http://alphafivewiki.com/Portability+Functions

[gamma]

Re: How Do show my field values in % using the summery value total?

You can also calculate over the column by using an aggregate function like SELECT SUM(extension) As Total_Extension FROM invoice_items WHERE INV_NUMBER = 1; (MySql example). For a list of the A5 portability functions to use see http://alphafivewiki.com/Portability+Functions

Hi Jim,

Thank you. I knew that, but I cannot enter an aggregate function without entering a �group by� which destroys the sense of using the Sum function in the first place. And I cannot just leave the Columns A, B and C out because I need them to calculate and I seem not to be able to use a select statement in the calculate field Expression as it is "client Side Property".

Furthermore I tried to use a SQL statement like:

SELECT
Group,
A,
(select A from View2 where Group ='Sum') As A_Total,
B,
(select B from View2 where Group ='Sum') As B_Total,
A,
(select C from View2 where Group ='Sum') As C_Total,
Sum
(select Sum from View2 where Group ='Sum') AS Total_Sum
FROM View1
WHERE Group <> 'Sum'

but I get following attached error from the SQLBuilder:

A5_Error_select_in_select_20111024.JPG

So currently I don�t have a solution as in v 10.5 I cannot create a Grid from a stored procedure.

If anybody else as a solution please let me know.
Thank you!

[gamma]

Re: How Do show my field values in % using the summery value total?

Hi,
Me again, just an update. I removed all unnecessary carriage returns from the SQL above and now the error message changes to only: "Property not found arr[] not found".

[gamma]

Re: How Do show my field values in % using the summery value total?

Hi,

here some feedback. There might be a nicer way to do this but here is what I have done:
1. I created a view with Cube

Create View [dbo].[PivotView] As 
SELECT CASE WHEN GROUPING([View1].PName)= 1 THEN 'Sum' ELSE PName End as PName, 
  
     SUM(CASE UName WHEN A Then [View1].calcValue ELSE 0 END) As 'A',  
     SUM(CASE UName WHEN B Then [View1].calcValue ELSE 0 END) As 'B',  
     SUM(CASE UName  WHEN C Then [View1].calcValue ELSE 0 END) As 'C',   
     SUM([View2].calcValue ) As Sum 
      
FROM [ProjectControlling].[dbo].[View1] GROUP BY PName
WITH CUBE

To create the pivot table dynamically follow the instructions on http://www.itrain.de/knowhow/sql/tsql/pivot/pivot2.asp (in German).
Further I created a view as mentioned in my post from 10-24-2011 10:42 AM (view2 = PivotView)

Then I created a grid with the SQL

SELECT PName,  
     if( A <> 0 , ((100 / A_TOTAL) * A), 0)  AS Percent_A,  
     if( B<> 0 , ((100 / B_TOTAL) * MKL), 0)  AS Percent_B,  
     if( C <> 0 , ((100 / C_TOTAL) * GBR), 0)  AS Percent_C,  
     if( Sum <> 0 , ((100 / Sum_TOTAL) * Sum), 0)  AS Percent_Sum 
FROM PivotView

It would be great to also create above columns in the grid dynamically depending on the existing amount of UNames in the SQL table. But for now this will do.
Thank you all for your help.