Skip to Content
0

Counting Customers Based On Calculated Measure

Feb 27, 2017 at 02:48 PM

35

avatar image

My goal is to define customers as single property or multi property based on number of collateral pieces. I created a [collateral count] measure. Where I'm stuck is counting how many of my customers have [collateral count]>1 and how many have [collateral count]=1.

My table looks like this

Customer Collateral Count

A 1

B 1

C 2

I need my result to be

Property Type Customer Count

Single 2

Multi 1

I've beet able to create a measure that indicates single or multi, but have not been able to count these items.

Any help would be appreciated!

Jay

What I have so far:

sample-data.png (5.9 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
AMIT KUMAR
Feb 27, 2017 at 03:14 PM
1

use sum function to get the total count for single or multi.

=Sum([Single variable])

Show 5 Share
10 |10000 characters needed characters left characters exceeded

Amit,

Thanks for the response, unfortunately this hasn't worked.

This is the logic behind single and multi:

Single: =Count([Customer Name]) Where ([Collateral Count]=1)

Multi: =Count([Customer Name]) Where ([Collateral Count]>1)

I tried to take the sum(Single) and it populated null, and when I tried to take the sum(Multi) I got a number in the thousands when it should be closer to 50 based on the data set.

Any ideas?

I've also tried to use =if([collateral count]>1;"Multi";"Single") and have been able to populate customers with the correct property indicator, and then used count([customer]) where ([measure]="Multi") but haven't had luck there either.

This is what is populating based on adding the Sum([single]) and Sum([Multi])

Thanks,

Jay

goal-sample.jpg (17.1 kB)
0

After doing measure=if([collateral count]>1;"Multi";"Single") try this.

=sum(if([measure]="Multi") then 1 else 0)

if it will work then similar for single

0

I think we're close, but still not working. The =if([collateral count]>1;"Multi";"Single") equation is identifying single v. multi, and the sum(=sum(if([measure]="Multi") then 1 else 0) is marking 1 for the correct items, but when I remove the customer dimension to aggregate by servicing user, the dimension gets ignored and it aggregates all of the collateral under each user and marks both users as having only 1 multi-property v identifying they each are attached to multiple customers each having a different measure.

update.jpg (13.4 kB)
current-update.jpg (29.9 kB)
0

you have to use Customer object in your formula with foreach context.

try like this

=sum(if([measure] foreach ([customer])="multi") then 1 else 0)

1

That worked perfectly. Thank you so much!

1