Create stacked column chart where 1st column is total, next 2 columns are segements of 1st column
I have a pivot table with 3 columns of values. Column 1 is the entire population, and columns 2 + 3 are segments of that population with filters applied. I want to create a stacked column chart in excel where columns 2 + 3 are part of Column 1. Instead, it is summing all 3 columns. Here is the data and chart currently. I want the orange and grey area to be part of blue (total) column, instead of the 3 columns added together.
See also questions close to this topic
Finding the cause of Excel file corruption
I have a feature that downloads things to an xls file using Apache POI. Mostly it works. But on one particular database, the resulting files are corrupted and won't open in Excel. I get the message "We found a problem with some content in 'DownloadFoo.xls'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes." . Clicking yes results in all the formatting, data validation, etc being stripped out. On the other hand, if I open the file in Open Office Calc and save it, it's fine and can be opened in Excel from then on. (The people who want to use these files aren't allowed to download Open Office Calc, so this is not considered an acceptable workaround.)
I have tried narrowing it down to see which data is causing the problem, but it seems to occur whenever 10 or more items are downloaded, regardless of which items they are. (On other databases, it's fine to download 100+). Excluding some of the columns helps, but they are perfectly innocuous looking columns (and virtually identical to other columns which are fine) so this still hasn't got me to the bottom of it.
Are there any techniques I could use to find out what Excel has a problem with in the corrupted spreadsheets?
I can't make major changes like getting it to download to xlsx instead as this feature is going to be scrapped and replaced with something completely different in the near future, so I'd like to just focus on the problem at hand.
Dynamically Creating Meta Titles from Excel
I can't seem to find a bit of code directly related to what I'm trying to accomplish, and that might be because there is some much much easier way to accomplish this task.
Basically I'd like to structure and change en-mass our company website Meta Titles. I'd like to build a title structure where I pull off columns from our database for -manufacturer sku- and -manufactuer & model-, then pump out dynamically created titles in this format:
(Manufacturer Sku) - (Manufacturer & Model) (Upsell/Call to Action) | (Company Name)
Now the Title should be less than 70 characters so the first three "cells" are variable character lengths, and the company name cell is always set.
So basically my thinking is that I need to make like 20 (Upsell) options of differing character lengths, so that when I combine columns I can get excel to pick the longest upsell option that makes the whole meta Title fit under 70 characters.
Most of the skus and models have differing character lengths but i'd like to be able to get in all of these elements because I think that would make for the best Meta Titles.
Am I making this too difficult for myself?
VBA Macro to count minutes
I was wondering if it would be feasible to do the task I'm about to explain via VBA or not. Basically what I'm trying to do is, go through "DUMMY2" and once the values are "27", I would like to subtract the beginning and end of the "DATETIME" in that range to get the minute count
For example: 05/16/2018 11:05 - 05/16/2018 10:50 --> to get 15 minutes. And then continue to go through the list skipping "16" , "9", "4" and doing the until it hits the second batch of "27" and goes through the macro again to calculate the minute count.
Thank you very much!
Sub Test() Cells.Find("DUMMY2").Offset(1, 0).Select Do Until ActiveCell.Value = "" Do While ActiveCell.Value > 26.8 And ActiveCell.Value < 27.1 First = ActiveCell.Offset(0, -2) 'maybe that doesn't make sense, but I'm trying to select the cells adjacent to "27" ActiveCell.Offset(0, -2).Select Loop ActiveCell.Offset(1, 0).Select Loop End Sub
5/16/2018 10:35 -0.03 0 5/16/2018 10:40 -0.04 0 5/16/2018 10:45 -0.04 12 5/16/2018 10:50 -0.32 27 5/16/2018 10:55 -0.27 27 5/16/2018 11:00 -0.23 27 5/16/2018 11:05 -0.21 27 5/16/2018 11:10 -0.14 16 5/16/2018 11:15 -0.01 9 5/16/2018 11:20 -0.02 4 5/16/2018 11:25 -0.32 27 5/16/2018 11:30 -0.31 27 5/16/2018 11:35 -0.30 27 5/16/2018 11:40 -0.29 27
Stacked MultiBarChart in nvd3 but should all start from offset 0
i have a data like:
1, 3, 6
2, 4, 10
I have tried with stackOffset, but i find nvd3 documentation difficult to master.
This is the serie 1, just grouped. I want the same height as in grouped, just "stacked" overlapped. I know the bar in the "back" would dissapear if it is not "high" enough.
- Set different Color of Stacked Bar series of Chart in c# in PPT
stacks bar graph within grouped and and without the sub-grouped plot
Someone already explained stacked bars within grouped bar chart here
test <- data.frame(person = c("group 1", "group 2", "group 3"), value1 = c(100, 150, 120), # male value2 = c(25, 30, 45) , # female value3 = c(25, 30, 45), # male value4 = c(100, 120, 150), # female value5 = c(10, 12, 15), # male value6 = c(50, 40, 70)) # female library(reshape2) # for melt melted <- melt(test, "person") melted$cat <- '' melted[melted$variable == 'value1' | melted$variable == 'value2',]$cat <- "sub group 1" melted[melted$variable == 'value3' | melted$variable == 'value4',]$cat <- "sub group 2" melted[melted$variable == 'value5' | melted$variable == 'value6',]$cat <- "sub group 3" melted$gender <- '' melted[melted$variable %in% sprintf("value%i",c(1,3,5)),]$gender <- "female" melted[melted$variable %in% sprintf("value%i",c(2,4,6)),]$gender <- "male" p = ggplot(melted, aes(x = cat, y = value, fill = gender)) p + geom_bar(stat = 'identity', position = 'stack') + facet_grid(~ person) + scale_fill_manual(values = c("orangered","dodgerblue2")) + theme(panel.background = element_rect(fill = 'white'))
I want to add another group but no subgroups within that group. is there any command or function that can do that in ggplot2?