Complex Aggr Question

[#1]

Hi, I am trying to create an expression that will identify stores with sales on each item, and sum their net sales for all items.  I can count the number of qualifying stores a simple count distinct function, but haven't been able to come up with the right AGGR function to return my desired results.  Here is a sample of what I am trying to acheive:

Data
STORE_NO	ITEM#	Item Sales
1	10	$ 1,000
1	11	$ 1,050
1	12	$ 1,075
2	11	$ 2,000
2	12	$ 2,020
2	13	$ 2,075
3	11	$ 3,000
4	11	$ 4,000
4	12	$ 4,050
4	13	$ 4,075
5	12	$ 5,000

STORE_NO	Net Store Sales
1	$ 11,000
2	$ 22,000
3	$ 33,000
4	$ 44,000
5	$ 55,000

Desired Result
ITEM#	Net Sales from Stores Selling
10	$ 11,000
11	$ 110,000
12	$ 132,000
13	$ 66,000

The 'Net Sales from Stores Selling' comes from summing Net Store Sales from Stores 1, 2, 3, 4 ($11,000+$22,000+$33,000+$44,000).  I thought i would have success with a sum(distinct Net Sales), but I want to qualifier to be STORE#, not Net Sales.  Here is the starting point of my expression:

=sum(aggr(sum({$<[Department#]=, [Category#]=,[Sub Category#]=, [Brand]=, [Vendor#]=, [ITEM#]=, 
[STORE_UPC]=>} Total <[STORE_NO]> Net Store Sales), [ITEM#], [STORE_NO]))

Thank you for reading my question, any help in the right direction will be very much appreciated!

[johnw]

For your example data, a simple sum("Net Store Sales") produces your desired result.  I assume I'm not understanding the problem.

[#3]

Thanks for looking at my question.  I will try to rephrase what I am trying to do.  In the calculation, I want to basically identify exact stores selling each item, then sum their total sales of all items.  If we have 3 stores, and stores 1 & 2 sold item A, I want to add the total net sales (items A-Z) for stores 1 & 2.  Then if stores 1 & 3 sold item B, I want to sum the total net sales (items A-Z) for stores 1 & 3.  I hope this is a clearer explanation, thank you again for your time.

[#4]

Give this a shot with item as the dimension:

=sum(aggr(if(sum([Net Sales]) > 0, sum(total <STORE> [Net Sales])),STORE))

So if the row is item 11, it should run through all of the stores and if they have a sum of [Net Sales] > 0, it should sum the entire store's sales (sum(total <STORE> [Net Sales])

You might need to add item in the aggr off the top of my head I'm not sure.

To be honest I dont' know if that syntax is going to work but I think the total <STORE> part should give you some direction at least... if that's what you wanted I'm not exactly sure.

[#5]

Hello,

If I undestand your need, you could use a set with P (possible) modificator:

Sum ({<STORE_NO=P({<[ITEM#]={11}>} STORE_NO )>}  [Item Sales])

I hope this helps !

Regards

[#6]

I have been able to get it to work in a sample qvw at the item level (except for the total, & I'm still having some problems in my actual app, but I guess as long as I have the concept down, I'll eventually get it).  When I try to go up a level to item category, the expression no longer returns my desired results.  Also, the subtotal is summing up rows, and if the calc was working correctly, it should be summing the store sales.  I'm attaching the test doc that I have been playing with.  Heres the expression that is working the best so far:

=sum(aggr(Sum(Total <[STORE#]> Qty*Price),[Category],[Product#],[STORE#]))

I appreciate the help!

[johnw · Accepted solution]

OK, so the second table in your sample data should be calculated from the first, doesn't exist in the data model, and should not have the values you show.  It should instead look like this if we built it as a chart:

STORE_NO,Net Store Sales
1,1000+1050+1075 = 3125
2,2000+2020+2075 = 6095
3,3000
4,4000+4050+4075 = 12125
5,5000

And so the chart you actually want should look like this:

ITEM#,Net Sales from Stores Selling
10,3125
11,3125+6095+3000+12125 = 25345
12,3125+6095+12125+5000 = 26345
13,6095+12125 = 18220

To get that, use this expression:

sum(aggr(sum({<"ITEM#"=>} total <"STORE_NO"> "Item Sales"),"ITEM#","STORE_NO"))

See attached.

Edit:  Looks like I was a bit too late, and you probably won't like my total line either.  Looking into it.

Edit: if(dimensionality(),sum(aggr(sum({<"ITEM#"=>} total <"STORE_NO"> "Item Sales"),"ITEM#","STORE_NO")),sum("Item Sales"))

Edit:  Ah, yeah, you're right.  You don't need the set analysis.  The "total" keyword has already told us to ignore item.

Edit:  No, I was right the first time.  You need the set analysis so the row totals don't change when you select an item.  I haven't figured out how to make the total work with selections yet.

Edit:  OK, this expression seems to work.  I'd hope there's a simpler way, but I'm not figuring it out.  See new attached file.

if(dimensionality()

,sum(aggr(sum({<"ITEM#"=>} total <"STORE_NO"> "Item Sales"),"ITEM#","STORE_NO"))

,sum({<"ITEM#"=,"STORE_NO"=P()>} total <"STORE_NO"> "Item Sales"))

[#8]

Great!!  Thank you again for getting me this far.  I think you're latest formula even gets me to what I thought was my next step in making it dynamic so I can have 1 expression that will calc correctly at any level (item, category, sub-cat, brand).  It seems to be working in my actual application as well

[#9]

I guess I'm not quite there on the dynamic part.  Is there a way to represent multiple fields in the aggr function?  I tried adding the different possible fields & separating them with commas, but it breaks the calc from working as I need.  I updated my sample doc with the added scenario.  Code that works:

=

if(dimensionality()
,sum(aggr(sum({<"Product#"=, "Category"=, "Brand"=, "SubCat"=, Invoice#=>}
total <"STORE#"> Qty*Price),"Category","STORE#")),
sum({<"Product#"=,"Category"=, "SubCat"=, "Brand"=,"STORE#"=P()>}Total <"STORE#"> Qty*Price))

It breaks when I modify it to this:

=

if(dimensionality()
,sum(aggr(sum({<"Product#"=, "Category"=, "Brand"=, "SubCat"=, Invoice#=>}
total <"STORE#"> Qty*Price),"Category","SubCat","Brand","STORE#")),
sum({<"Product#"=,"Category"=, "SubCat"=, "Brand"=,"STORE#"=P()>}Total <"STORE#"> Qty*Price))