Show implicit field Count

[JustinDallas]

Hello Folks,

So here is my test data:

Fact:
LOAD *,
If( LEN([Bill To]) = 0, Null(), [Bill To]) AS [Bill To Id], 
If( [Stop Number] = '-', Null(), [Stop Number]) AS [Stop #]
;
LOAD * Inline
[
	'Bill To' , 'Order #', 'Stop Number',
    'ZAXBYS', 111, '-'
    'BOJANGLES', 222, '-'
    ,111,1000
    ,111,1001,
    ,222,3000,
    ,222,4000,
    ,222,5000,
]
;
DROP FIELDS "Stop Number", "Bill To" 
;
EXIT Script
;

What I want to do is "Show Count of stops by BillTo".  As you can see, the Bill Tos are related to Order #s and the Order #s are related to Stop #s.  However, I'm not able to even start to figure out what I need to do .  Just a dumb list-and-sum gives me this:

My desired output looks like so:

What I've tried/thought:

My first idea is that I am trying to say the following in Set Analysis;

"Given a [Bill To] dim, Count all the [Stop Number]s based off of the Possible [Order #]s.  

This leads me to believe that I need to use the P() function in my set analysis, but I'm not sure.  I've also considered that I may need to use AGGR( ) since I've playing around with dimensions, at least implicitly, but I'm not sure about that either.

Any help is greatly appreciated.

[crusader_ · Accepted solution]

Hi,

Please try below expression and exclude NULL values from your dimension:

sum(aggr(count(TOTAL <[Order #]> [Stop #]),[Bill To Id], [Order #]))

Hope this helps.

//Andrei

[JustinDallas]

Wow, I would have never, ever, ever guessed that was the solution.  Thanks.

[JustinDallas]

I understand the bulk of the Set Analysis, but do you mind explaining why you have "TOTAL <Order #>" there? 

[crusader_]

Sure,

As you probably know AGGR function create virtual table with Expression and listed dimensions...

aggr(count(TOTAL <[Order #]> [Stop #]),[Bill To Id], [Order #])

Virtual table looks like below

Bill To Id   |   Order #  |  Count(Stop#)

ABC     |   1    |  <here come the trick>

You don't want to count only Stop # per line combination, but get a total number of Stop # with this Order #, so you get

Bill To Id   |   Order #  |  Count(Stop#)

ABC     |   1    |  3

Hope this helps.

//Andrei