
1. Re: SUMIF Calculation in Order to assign
Michael Hesser Jul 22, 2016 12:52 PM (in response to yassen.shahansky)2 of 2 people found this helpfulHi Yassen;
I think a LOD calculation will get you what you need.
Total Order Amount
{FIXED [Order #]:sum([$ Amount])}
This should generate your total order amount, based on Order #.
Your Order Size Bucket can be based off this new LOD calculation using nested IFs.
When you are creating your view, place [Total Order Amount] first so you can sort on it; you can hide it so people don't see it (this lets you get your Order Size Buckets to line up the way you want). Include it second time so your columns will look like:
[Total Order Amount] [Order #] [Item] [$ Amount] [Total Order Amount] [Order Size Bucket]
Do you think this will give you what you need?

2. Re: SUMIF Calculation in Order to assign
Derrick Austin Jul 22, 2016 12:49 PM (in response to yassen.shahansky)Hey Yassen,
I know it doesn't answer your question, but as an aside  You can replicate the "SUMIF" functionality of Excel by nesting an IF statement inside the SUM.
SUM(IF [x] = 2 THEN [z] END)

3. Re: SUMIF Calculation in Order to assign
Jessica LastName Jul 22, 2016 1:09 PM (in response to Michael Hesser)1 of 1 people found this helpfulLOD (Level of Detail) calculations are a very handy tool to have.
Another option for the bins, if they are all going to be $25 in size is something like the following:
'$' + STR(INT([Total Order Amount]/25) * 25 + 1) + '  ' + STR(INT([Order Total]/25) * 25 + 25)
An advantage here is that bins are automatically created for your data, so you won't need to keep adding nesting for bigger orders.
Set this calculated field to a dimension and then you can easily make things like this:
If you want bins on a log scale, i.e. $19, $1099, $100999, try:
'$' + STR(10^(INT(LOG([Order Total])))) + '  ' + STR(10^(INT(LOG([Order Total])) + 1)  1)
and set the calculated field to a dimension as before.
Hope this is of use!

4. Re: SUMIF Calculation in Order to assign
Michael Hesser Jul 22, 2016 1:24 PM (in response to Jessica LastName)Awesome point, Jessica! I don't use bins much, but this is a perfect use.

5. Re: SUMIF Calculation in Order to assign
yassen.shahansky Jul 27, 2016 2:04 PM (in response to Michael Hesser)Hi Michael,
Yes, this was very helpful. Thank you!
Obviously this is not my actual data set which is quite a bit more complicated; however, I was able to aggregate the $ Amount based on the Order # with the LOD calculation information you provided me with.
One more question, is there a way to add additional level of detail using this calculation? For example, if I want to aggregate the $ amount based on the order # and then based on the Products within the Order #? I did some reading through the support section and was not able to get there.
Thanks again!
Yassen

6. Re: SUMIF Calculation in Order to assign
yassen.shahansky Jul 27, 2016 2:09 PM (in response to Jessica LastName)Hi Jessica,
Thank you for your reply! I almost got there with the LOD calculation. Now I am just looking to add an additional level of detail/criteria based on which to aggregate the $ Amount. I forgot to mention this in my original post but the actual bins vary in size and there are only 56 of them. The data in my original post is completely made up to illustrate what I have in front of me and what I am looking to achieve.
Thanks!
Yassen

7. Re: SUMIF Calculation in Order to assign
Michael Hesser Jul 28, 2016 7:17 AM (in response to yassen.shahansky)1 of 1 people found this helpfulHello Yassen;
Yes... all you need to do is add the field to a second LOD expression:
{FIXED [Order #], [Item]:sum([$ Amount])}
You might also be able to do this using Totals and Subtotals based on how your data is presented, but I'm a little more familiar with using LOD to get the values I need.
Please let me know if this works for you!

8. Re: SUMIF Calculation in Order to assign
Michael Hesser Aug 8, 2016 5:26 AM (in response to yassen.shahansky)Hello Yassen;
Was my answer(s) correct? If so, would you mark it as such? That way you won't have other people trying to answer your question and this post can be closed. I'll also get a little credit