w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
How to subtotal multiple columns
Given the circumstances, here's a hacky way to do it (assuming a fixed first, second, third and fourth column number (example): SELECT band, (SELECT COUNT(*) FROM votes WHERE first = band) AS first, (SELECT COUNT(*) FROM votes WHERE second = band) AS second, (SELECT COUNT(*) FROM votes WHERE third = band) AS third, (SELECT COUNT(*) FROM votes WHERE fourth = band) AS fourth FROM ( SELECT DISTINCT band FROM ( SELECT first AS band FROM votes UNION ALL SELECT second AS band FROM votes UNION ALL SELECT third AS band FROM votes UNION ALL SELECT fourth AS band FROM votes ) x ) y -- optionally: ORDER BY band However, I would recommend normalizing the data so the bands are in their own table: -- First create a table to store the valu

Categories : Mysql

Subtotal of all items in a query
Afraid I think the best solution is to cross join against a subselect:- SELECT purchase_order.id AS POID, purchase_order_items.description AS Item, purchase_order_items.quantity AS ItemQTY, purchase_order_items.price AS ItemPrice, (purchase_order_items.quantity*purchase_order_items.price) AS ItemTotal, Sub1.FullTotal FROM purchase_order INNER JOIN purchase_order_items ON purchase_order.id = purchase_order_items.fk_purchase_order CROSS JOIN ( SELECT SUM(purchase_order_items.quantity*purchase_order_items.price) AS FullTotal FROM purchase_order INNER JOIN purchase_order_items ON purchase_order.id = purchase_order_items.fk_purchase_order ) Sub1 To bring it back for grouped purchase order ids SELECT purchase_order.id AS POID, purchase_order_items.description AS Item, purchase_

Categories : Mysql

Ways to Calculate rolling subtotal
Just use a subquery as the expression: SELECT id, dt, val, ( SELECT SUM(val)/12 FROM mytable t2 WHERE t2.id = t.id AND t2.dt > DATEADD(mm, -12, t.dt) AND t2.dt < t.dt ) val12MonthAvg FROM mytable t However with millions or rows it's likely to be very slow.

Categories : SQL

dividing cells by subtotal of a dataframe in R
Like this? DF <- read.table(text="district_i year party votes 1 2001 party1 24 2 2001 party1 56 3 2001 party1 12 1 2002 party1 40 2 2002 party1 749 3 2002 party1 26 1 2001 party2 34 2 2001 party2 48 3 2001 party2 23 1 2002 party2 34 2 2002 party2 48 3 2002 party2 98", header=TRUE) library(plyr) ddply(DF, .(year,party), transform, contrib = votes / sum(votes)) # district_i year party votes contrib # 1 1 2001 party1 24 0.26086957 # 2 2 2001 party1 56 0.60869565 # 3 3 2001 party1 12 0.13043478 # 4 1 2001 party2 34 0.32380952 # 5 2 2001 party2 48 0.45714286 # 6 3 2001 party2 23 0.21904762 # 7 1 2002 party1

Categories : R

Invalid Parameter SubTotal in PayPal MPL iOS SDK
That's correct - here are the limits for each currency: https://www.paypal.com/us/webapps/helpcenter/helphub/article/?solutionId=11637&m=SRE

Categories : IOS

SUMIF Horizontal
It should be SUMIF not IFSUM - that works for any range, vertical or horizontal or even multiple rows/columns, as long as the value to return is numeric =SUMIF(A1:C1,"Y",A2:C2) AS Alan says, HLOOKUP would also give you the correct result and can return text as well as numbers =HLOOKUP("Y",A1:C2,2,0)

Categories : Excel

trying to multiply subtotal with a shipping % to get grand total
Ryan, You just have to write <span class="TextB">Total: $<?php echo ($SHOWMEQ*3/100)+$SHOWMEQ;?></span> It will display total amount along with 3% shipping and handling charges.

Categories : PHP

Calculate the subtotal while every time adding a new row in JTable
There are probably a number of ways to do this and it will come down to your requirements about how you would finally achieve it, but to my mind, you want to minimize the number of iterations you need to perform over the table each time a new row is added... There's no point iterating over the entire table if only one row was added, for example. It's inefficient. Instead, you could use TableModelListener and listen for updates to the table model and update a running total (adding the new row values to already running total). import java.awt.BorderLayout; import java.awt.Component; import java.awt.Dimension; import java.awt.EventQueue; import java.awt.GridBagConstraints; import java.awt.GridBagLayout; import java.awt.event.ActionEvent; import java.awt.event.ActionListener; import java

Categories : Java

Display SubTotal and Total at the same time - Javascript
you can add this piece of code in your cycle, or move out of cycle to update only current tr's cell. In the CalculateEstimatedSavings function form_field.parentElement.nextElementSibling.getElementsByTagName('span')[0].textContent="$" + round_decimals( item_quantity * item_price, 2) http://jsfiddle.net/Srq9v/1/ look here beware of nextElementSibling function, I'm not sure from when IE supports it.

Categories : Javascript

Calculate SUBTOTAL of a SUMPRODUCT without a column for total
You can use this approach =SUMPRODUCT(B2:B4,SUBTOTAL(9,OFFSET(C2:C4,ROW(C2:C4)-MIN(ROW(C2:C4)),0,1))) that will give you the same result as before with everything visible but if you filter the second part will give you zero for the hidden rows and hence will effectively ignore them

Categories : Excel

PHPExcel Sumif and Skip
Build the formula dynamically in your PHP script: $startCell = 'A'; $endCell = 'H'; $row = 1; $formula = '=' . $startCell . $row; while ($startCell++ != $endCell && $startCell++ != $endCell) { $formula .= '+' . $startCell . $row; } EDIT Note that I've also added a pure Excel formula answer to your previous question

Categories : PHP

sumif function in R of two matrices
How about: A[B>=3] <- NA rowSums(A,na.rm=TRUE) ## [1] 3 1 7 or @Roland's suggestion (even shorter): rowSums(A*(B<3)) which takes advantage of the fact that TRUE gets coerced to 1 and FALSE gets coerced to 0 when doing numerical operations ...

Categories : R

Using the current SUMIF "row" in criteria
ROW() will return the number of the current row. But the first argument to VLOOKUP is a cell reference or value. If you explain what value you are trying to use in the VLOOKUP, how it is related to the current cell, then it is probably possible to use INDEX and MATCH (perhaps OFFSET) to achieve what you need.

Categories : Excel

How to do the row to column SUMIF in MS SQL Server
You can try like this.. Select Person, Sum(Part)as Part, Sum(Case when Status='A' then 1 End) AS Status-A, Sum(Case when Status='B' then 1 End) AS Status-B, Sum(Case when Status='C' then 1 End) AS Status-C from Table-A Group By Person;

Categories : Sql Server

Change cart subtotal to reflect discount in woocommerce
I suggest you file a bug report for that in WooCommerce. For now you should just make the 20% discount coupon unusable in conjunction with other coupons. You can do this by editing the coupon and check the "individual use" check box.

Categories : Wordpress

Display grand total as the sum of averages (custom subtotal)
Try adding this into a new column as a test: RunningValue(Avg(Fields!MemberCount.Value,"RowGroup_Category"),SUM,Nothing) If the value is correct you should be able to change SUM into MAX when setting this expression in the grand total field. You can refer to the total like code.TotalMemberCount instead of using a get function but i don't think you need this function in this case. Check the following blog for a simular variable referencing situation

Categories : Asp Net

Taking 3 or more combinations using `combinations`
I'm not good at math, but if the question is "How would I then sum each of the triples I get in v?" then the answer is: sv = map(sum, v) where sv will contain a list (an iterator actually) of sums of triples from v

Categories : Python

Nested aggregate error with SSRS subtotal and grand total
Based on your description above, it seems like you're working out Contribution to the Classification level, e.g. Equities, Fixed Income. As such, when working out the total ACB for detail rows you need to work out the total in that particular Classification Scope. Consider some simplified data: I've called this DataSet Performance and have constructed a report based on it: For the row level Contribution I used the expression: =Fields!ACB.Value / Sum(Fields!ACB.Value, "Classification") * (Fields!TotalGain.Value / Fields!ACB.Value) Here the Scope of the SUM expression is the group level. For the sub total I used the expression: =Sum(Fields!ACB.Value) / Sum(Fields!ACB.Value, "Classification") * (Sum(Fields!TotalGain.Value) / Sum(Fields!ACB.Value)) To me, these two expressio

Categories : Reporting Services

Changing cell reference for a SUMIF criterion?
=SUMIF('range',INDEX(A2:C2,MATCH("zzzzz",A2:C2)),'range') seems worth a try. Edit Too long for a comment: I don’t really have any idea how =MATCH() works but think of it as traversing left to right while searching. When it fails to find a match it just happens to have been looking at the last entry and (conveniently!) offers that up. So the key is to look for something that won't be found. For people’s names I chose “zzzzz” as unlikely to be present. For numbers anything up to 9.99999999999999E+307 is theoretically possible and hence some people use that (eg an hour ago Formula to get the first cell with a numerical value from a selection?) but I prefer a googol (1E+100) and that seems quite large enough, is much shorter – and easier to remember, even if a few 9

Categories : Excel

SUMIF formula with date criteria not working
If your cell date amounts are stored as formatted date amounts, change your formula to: =SUMMEWENNS(Rawdata!K2:K3446;Rawdata!I2:I3446;"bezahlt";Rawdata!A2:A3446;">="&DATWERT("04.03.2013 00:00");Rawdata!A2:A3446;"<="&DATWERT("10.03.2013 23:59"))

Categories : Excel

Product With SUMIF Function When Using ROUND Function
I have tried using SUMIF and SUMIFS, but both result in errors. Neither likes the use of the ROUND function That is because SUMPRODUCT can take in array values while SUMIF cannot. I'm trying to find a way to get the same results as the following function, but without using SUMPRODUCT, since SUMPRODUCT is not supported in Documents To Go for iPhone. I do not see a way to implement that exact functionality without having the formula or changing the spreadsheet slightly. You can get close by using =ROUND(SUMIF($D$2:$D$50000,"O",$C$2:$C$50000),0) which will round the end value instead of each individual value. However you could change the spreadsheet by making another column (lets say E) that is just the rounded version of column C and use =SUMIF($D$2:$D$50000,"O",$E$2:$E$50000)

Categories : Excel

Excel VBA - Apply sumif to only a specific coloured cell - referencing the above coloured cell
The best way to do this would be to think about how you would do this manually. i.e. 1) Start at top of Column K. 2) Move down column K until blank 3) Enter SUMIF Statement using range of last grey cell + 1 to current gray cell - 1 4) Loop until bottom of data So... Private Sub loopSumIF() Dim currentRow As Integer Dim lastGreyRow As Integer Dim endLoop as Boolean currentRow = 2 lastGreyRow = 2 endLoop = False Do Until endLoop = True if ActiveSheet.Range("K" & currentrow) = "" then ActiveSheet.Range("K" & currentRow) = "=SUMIF(K" & lastGreyRow & ":K" & currentorw - 1 & "," & Chr(34) & ">0" & Chr(34) & ")" 'set formula lastGreyRow = currentRow + 1 'set the top of the next sumif to the next cell down End If curre

Categories : Excel

How to use combinations
Use range(1, len(lis)+1) to get the value for the second parameter(r) that is passed to combinations. or range(2, len(lis)+1) if you want to start from 2. >>> from itertools import combinations >>> lis = [[0,0,0],[0,0,1],[0,1,0]] >>> for i in range(1, len(lis)+1): ... for c in combinations(lis,i): ... print c ... ([0, 0, 0],) ([0, 0, 1],) ([0, 1, 0],) ([0, 0, 0], [0, 0, 1]) ([0, 0, 0], [0, 1, 0]) ([0, 0, 1], [0, 1, 0]) ([0, 0, 0], [0, 0, 1], [0, 1, 0]) As pointed out may @abarnert in the comment, may be you want this: >>> from pprint import pprint >>> from itertools import chain >>> flatten = chain.from_iterable >>> ans = [list(flatten(c)) for i in range(2, len(lis)+1) for c in permutations(lis,i)] >

Categories : Python

All combinations of all sizes?
Perhaps combn in conjunction with lapply might be helpful: x <- 1:4 lapply(seq_along(x), function(y) combn(x, y)) # [[1]] # [,1] [,2] [,3] [,4] # [1,] 1 2 3 4 # # [[2]] # [,1] [,2] [,3] [,4] [,5] [,6] # [1,] 1 1 1 2 2 3 # [2,] 2 3 4 3 4 4 # # [[3]] # [,1] [,2] [,3] [,4] # [1,] 1 1 1 2 # [2,] 2 2 3 3 # [3,] 3 4 4 4 # # [[4]] # [,1] # [1,] 1 # [2,] 2 # [3,] 3 # [4,] 4 As @Roland points out, there is also a simplify argument to combn that when set to FALSE would create a nested list of individual column vectors rather than a matrix of all the results. For example, instead of list item [[3]] above being presented as a matrix, if you used lapply(seq_along(x), function(y

Categories : R

Combinations from an array in .NET
I think it's quite useless as a program, and as an exercise if you don't do it yourself then it's useless. But I give everyone what they ask... int[] arr = new int[] { 1, 2, 3 }; // How much elements in each permutation for (int i = 1; i <= arr.Length; i++) { // Starting point of the permutation for (int j = 0; j < arr.Length; j++) { Console.Write("["); // Single element of the permutation for (int k = 0; k < i; k++) { if (k != 0) { Console.Write(", "); } Console.Write("{0}", arr[(j + k) % arr.Length]); } Console.WriteLine("]"); // Single cycle for last permutation of length arr.Length if (i == arr.Length) { break;

Categories : Dotnet

List all the possible combinations
As stated in the comments Eric Lippert has a blog post named Computing a Cartesian Product with LINQ that explains how to solve your problem. You need an extension method to compute the cartesian product: public static IEnumerable<IEnumerable<T>> CartesianProduct<T>(this IEnumerable<IEnumerable<T>> sequences) { IEnumerable<IEnumerable<T>> result = new [] { Enumerable.Empty<T>() }; foreach (var sequence in sequences) { var localSequence = sequence; result = result.SelectMany( _ => localSequence, (seq, item) => seq.Concat(new[] { item }) ); } return result; } Then you need a sequence of sequences to perform the product over. In your case you have both strings and integers in your sequences so the common

Categories : C#

What are recommended JAX-RS combinations?
As Tichodroma pointed out, what I was actually looking for is a lightweight Java EE 6 container featuring JAX-RS, ideally a Java EE 6 Web Profile implementation with JAX-RS. Luckily, TomEE JAXRS seems to be exactly what I was looking for: http://tomee.apache.org/comparison.html

Categories : Java

Multiplying vector combinations
I believe the function you're looking for is outer > outer(cir, rpm, function(X, Y) X * Y / 63360 * 60) [,1] [,2] [1,] 8.924979 20.82495 [2,] 11.602473 27.07244 [3,] 12.941220 30.19618 In this case you could clean up the notation a bit: outer(cir, rpm / 63360 * 60)

Categories : R

Find combinations of objects in R
From library(gtools): combinations(3,2,c("sample1","sample2", "sample3")) Result: [,1] [,2] [1,] "sample1" "sample2" [2,] "sample1" "sample3" [3,] "sample2" "sample3" The same result can be obtained if those objects are named elements of a list: tmp <- list(sample1=1:3,sample2=4:6,sample3=7:9) combinations(3,2,names(tmp)) Or, if those objects are all in an environment: tmp <- new.env() tmp$sample1 <- 1:3 tmp$sample2 <- 4:6 tmp$sample3 <- 7:9 combinations(3,2,objects(tmp))

Categories : R

php generate all combinations from given array
Like this one: $a = array('A' => array(1, 2), 'B' => array(3, 4), 'C' => array(5)); function get_combinations($arrays) { $result = array(array()); foreach ($arrays as $property => $property_values) { $tmp = array(); foreach ($result as $result_item) { foreach ($property_values as $property_value) { $tmp[] = array_merge($result_item, array($property => $property_value)); } } $result = $tmp; } return $result; } Output var_dump(get_combinations($a)); array (size=4) 0 => array (size=3) 'A' => int 1 'B' => int 3 'C' => int 5 1 => array (size=3) 'A' => int 1 'B' => int 4 'C' => int 5 2 =>

Categories : PHP

Enumerating combinations by product
Let's say we want to get a time complexity of O(N * K) where N is the number of lists and K is the K-th product. We maintain a pointer at the beginning of each list (for simplicity I call this P[i] - i'th pointer at list i, and L[i] - the i'th list). Initially P[i] = 0 for each list (because we start indexing the lists from 0) Step 1: The first product is L[0][P[1]] * L[1][P[2]] * .. L[N][P[N]]. Step 2: For the next step we are interested in minimizing the next product. We select j (0<=j<=N) for which L[j][P[j] + 1] is minimum. We increment P[j] by 1 and then go at Step 1 for computing the next product. I think it's clear that the next product should be the minimum of all possibilities. If you want to count the number of unique products you simply maintain a counter which you

Categories : Misc

Combinations test data
With the brand new Python 2.6, you have a standard solution with the itertools module that returns the Cartesian product of iterables : import itertools print list(itertools.product([1,2,3], [4,5,6])) [(1, 4), (1, 5), (1, 6), (2, 4), (2, 5), (2, 6), (3, 4), (3, 5), (3, 6)] You can provide a "repeat" argument to perform the product with an iterable and itself: print list(itertools.product([1,2], repeat=3)) [(1, 1, 1), (1, 1, 2), (1, 2, 1), (1, 2, 2), (2, 1, 1), (2, 1, 2), (2, 2, 1), (2, 2, 2)] You can also tweak something with combinations as well : print list(itertools.combinations('123', 2)) [('1', '2'), ('1', '3'), ('2', '3')] And if order matters, there are permutations : print list(itertools.permutations([1,2,3,4], 2)) [(1, 2), (1, 3), (1, 4), (2, 1), (2, 3), (2,

Categories : Testing

Excel vba Create combinations in same row each one
Here is a way to do it: In your excel sheet, add an array formula like this: A B C D E 1 2 A B C D {=k_combinations(CONCATENATE(A2;B2;C2;D2);3)} 3 E F G H {=k_combinations(CONCATENATE(A3;B3;C3;D3);3)} Note that you should extend the array formula to columns F, G, H and so on so that you get all results. (The { and } are not to be inserted manually, they are the mark of the array formula) : Select cells E2, F2, G2, H2, and so on to Z2 Type the formula To validate input, press Ctrl+Shift+Enter Put the following code into a code module. Public Function k_combinations(ByVal chLetters As String, ByVal k As Long) As Variant Dim chCombinations() As String Dim uCount As Long Dim vReturn() As Variant Dim i As Long uCount =

Categories : Vba

Caculating total combinations
This Python code computes the answer in O(nm) by keeping track of the numbers ending with a particular digit. Different arrays (A,B,C,D) are used to track numbers that have hit the maximum or minimum of the range. n=3 m=6 A=[1]*n # Number of ways of being at digit i and never being to min or max B=[0]*n # number of ways with minimum being observed C=[0]*n # number of ways with maximum being observed D=[0]*n # number of ways with both being observed A[0]=0 # Cannot start with 0 A[n-1]=0 # Have seen max so this 1 moves from A to C C[n-1]=1 # Have seen max if start with highest digit t=0 for k in range(m-1): A2=[0]*n B2=[0]*n C2=[0]*n D2=[0]*n for i in range(1,n-1): A2[i]=A[i+1]+A[i-1] B2[i]=B[i+1]+B[i-1] C2[i]=C[i+1]+C[i-1] D2[i]=D[i+1]

Categories : Algorithm

Select a subset of combinations
Your approach: op <- function(){ ncomb <- combn(20, 7) ncombsub <- ncomb[, sample(choose(20,7), 5000)] return(ncombsub) } A different strategy that simply samples seven rows from the original matrix 5000 times (replacing any duplicate samples with a new sample until 5000 unique row combinations are found): me <- function(){ rowsample <- replicate(5000,sort(sample(1:20,7,FALSE)),simplify=FALSE) while(length(unique(rowsample))<5000){ rowsample <- unique(rowsample) rowsample <- c(rowsample, replicate(5000-length(rowsample), sort(sample(1:20,7,FALSE)),simplify=FALSE)) } return(do.call(cbind,rowsample)) } This should be more efficient because it prevents you from having to calculate all

Categories : R

all possible combinations between certain amount of digits c#
I'll direct you to Eric Lippert's article on implementing a Cartesian Product in Linq, which he writes as an extension method. static IEnumerable<IEnumerable<T>> CartesianProduct<T>(this IEnumerable<IEnumerable<T>> sequences) { IEnumerable<IEnumerable<T>> emptyProduct = new[] { Enumerable.Empty<T>() }; return sequences.Aggregate( emptyProduct, (accumulator, sequence) => from accseq in accumulator from item in sequence select accseq.Concat(new[] {item})); } Using this, you can implement your method like this: static IEnumerable<string> GetAllPossibleCombinations( int lengthofeachintreturnedinarray, IEnumerable<string> typesofelementsusedincombinations) { return Enumerable

Categories : C#

Grouping algorithm for combinations
Take each input and treat it as it's own group. So, for example, Cat, Blue, Long becomes the group of [Cat], [Blue], [Long], which each category having exactly one item. Go through each group in your list, starting with the first. Pair it with each other group in the list. Combine those pairs of groups into a single group if they meet the appropriate criteria. The criteria for merging groups is if the set of values for n-1 of the categories are the same, and exactly one category set does not match. If that is the case, create a new group with the n-1 similar categories being the same, and the remaining category the intersection of the sets. If you find a match, stop comparing pairs and start over with the first item in the first group. (Using deferred execution here helps you, so

Categories : C#

The set of all combinations of vectors in Python
This should get you started: import numpy as np import itertools as it def row_product(*arrays): lengths = np.array([x.shape[0] for x in arrays]) positions = np.cumsum(lengths) ranges = np.arange(positions[-1]) ranges = np.split(ranges,positions[:-1]) total = np.concatenate((arrays),axis=0) inds = np.fromiter(it.chain.from_iterable(it.product(*ranges)), np.int) inds = inds.reshape(-1, len(arrays)) return np.take(total, inds, axis=0) The last dimension(s) must be the same. Showing the results: a=np.array([[2,0],[1,1],[2,0]]) b=np.array([[1,0],[0,1]]) c=np.array([[0,0]]) print row_product(a,b,c) [[[2 0] [1 0] [0 0]] [[2 0] [0 1] [0 0]] [[1 1] [1 0] [0 0]] [[1 1] [0 1] [0 0]] [[2 0] [1 0] [0 0]] [[2 0] [0 1] [0 0]]]

Categories : Python

Python all combinations of data
You should use the itertools library. You want to generate all unique permutations of each element in the powerset. Some code might look like from itertools import permutations, combinations, chain # Taken from itertools page, but edited slightly to not return empty set def powerset(iterable): "powerset([1,2,3]) --> (1,) (2,) (3,) (1,2) (1,3) (2,3) (1,2,3)" s = list(iterable) return chain.from_iterable(combinations(s, r) for r in range(1, len(s)+1)) Then In [1]: s = ('A', 'B', 'C') In [2]: [j for i in powerset(s) for j in permutations(i)] Out[2]: [('A',), ('B',), ('C',), ('A', 'B'), ('B', 'A'), ('A', 'C'), ('C', 'A'), ('B', 'C'), ('C', 'B'), ('A', 'B', 'C'), ('A', 'C', 'B'), ('B', 'A', 'C'), ('B', 'C', 'A'), ('C', 'A', 'B'), ('C', 'B', 'A')]

Categories : Python

Creation of all possible combinations with SQL Server
Here's how to get up to 4 levels (your sample data only requires 3, but I wanted to make sure this worked beyond that). You should be able to follow the pattern to go up to 7, 10, what have you. Oh, and don't expect this to be fast. ;WITH z AS ( SELECT i,inm,si,snm,truth,c FROM ( SELECT i = i.ItemId, inm = i.Name, si = isi.SubItemId, snm = s.Name, c = COUNT(isi.SubItemId) OVER (PARTITION BY i.ItemId) FROM @Item_SubItem AS isi INNER JOIN @Item AS i ON isi.ItemId = i.ItemId INNER JOIN @SubItem AS s ON isi.SubItemId = s.SubItemId ) AS y CROSS JOIN (VALUES('true'),('false')) AS t(truth) ) SELECT Item = z1.inm, SubItems = COALESCE( z1.snm + ' = ' + z1.truth,'') + COALESCE(', ' + z2.snm + ' = ' + z2.truth,'') + COALESCE(', ' +

Categories : SQL



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.