nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

chart series how to do a percentage series

dmoore

I want a percentage of sum(numValue) / sum(denValue) 
with my query being SELECT numValue, denValue, ... area, state, organization ... 
I don't think I can do any type of sum in my query 
in the chart GUI there is expression builder but I don't see any type of expressions to do sum
and in the aggregate expression drop down just below the series there is these 
None 
Moving Average 
Running Sum 
Running NPV 
Rank 
is... 
Percent Rank 
Percent Sum 
Running Count 
this calculation is basically the percentage of the num/den of the data that will be shown - but the data will have to be filtered to certain area, or state, or org etc 
if you say the calculation has to be done in the query I don't see how I can group data by say or by org on the fly if those values are in the query.

Find more posts tagged with

Comments

mwilliams

So, you want the percentage of the overall? Not just of the values shown in the chart? Is this correct?

dmoore

example: 
my data has 20 rows/ reportDate that should make up 1 series 
select numValue, denValue, ReportDate, organization from tbl 
the x series = month/year = Jan 2014, Feb 2014, ... 
the y series = (sum(numValue) / sum(denValue)) * 100 = 48%, 52%, 38%, 42%, ... 
so it needs to group up the sum of all the numerators and sum of all the denominators and make the calculation 
does the sum(numValue) HAVE to be done in the query so that I only get 1 row / reportDate ? or can this calculation be done in the GUI ?

mwilliams

Can you provide a small sample of what the data looks like in your data set?

dmoore

num | den | reportDate | org | site 
3 4 1/1/2014 org1 site1 
2 4 1/1/2014 org1 site2 
1 5 1/1/2014 org1 site3 
2 4 1/1/2014 org2 site4 
2 4 1/1/2014 org2 site5 
1 4 1/1/2014 org2 site6 
3 4 2/1/2014 org1 site1 
1 4 2/1/2014 org1 site2 
2 5 2/1/2014 org1 site3 
2 5 2/1/2014 org2 site4 
1 5 2/1/2014 org2 site5 
1 5 2/1/2014 org2 site6 
I should end up with 2 series lines (org1 and org2) 
percent(sum(num) / sum(den)) 
below the dates are x axis and the values are y axis (should be formatted as xx%) 
series org1 
1/1/2014 (sum(3+2+1) / sum(4+4+5)) * 100 
2/1/2014 (sum(3+1+2) / sum(4+4+5)) * 100 
series org2 
1/1/2014 (sum(2+2+1) / sum(4+4+4)) * 100 
2/1/2014 (sum(2+1+1) / sum(5+5+5)) * 100

mwilliams

Take a look at this report. I use a table to group the values, then use the optional chart view of a table to use the table's groupings as fields for the chart. I think this does what you're wanting. Let me know.

dmoore

i was able to open and run this report and see how you added the column bindings to do it correctly. 
I currently have just the developer and when I open report > change property editor - chart TO Bindings 
I here see the available column bindings which you created. 
all the edit | add buttons are greyed out. 
is this feature only allowed in the professional (paid) version?

mwilliams

They're grayed out in the chart because it's using the bindings from the table. I created all the bindings I needed in the table view, then set up the chart view using those bindings. If you ever use another element for your data, the options are going to be fairly limited.