Grouping assettypes by customer

[tommyl]

Hello,

I have a data like:

customerid	assetType
1	pstn
2	pstn
3	pstn
4	adsl
1	adsl
2	adsl
3	pstn
4	adsl

i want to show grouping in a pie chart as below:

PSTN	PSTN and ADSL	ADSL
1	2	1

i cannot solve the dimension. In order to get this grouping what should i write in dimension? i did this but the counts does not match with db:

=aggr(concat(distinct assetType,' and '), customerid)

thank you

Regards,

[Anil_Babu_Samineni]

Can you rephrase the question?

[Chirag2397]

Hi,

Try this

Dimension:  =ValueList('adsl','adsl and pstn','pstn')

Measure: =if(ValueList('adsl','adsl and pstn','pstn')='adsl',
Count({<customerid=e({<assetType={'pstn'}>}customerid)>}customerid),
if(ValueList('adsl','adsl and pstn','pstn')='pstn',
Count({<customerid=e({<assetType={'adsl'}>}customerid)>}customerid),
Count({<customerid=p({<assetType={'adsl'}>}customerid)>*<customerid=p({<assetType={'pstn'}>}customerid)>}customerid)
))

[kaanerisen · Accepted solution]

Hi Tommyl,

Dimenison : aggr(Concat(distinct assetType,' and '),customerid)

Measure : Count(distinct customerid)

Hope it helps..

[Vegar]

@kaanerisen  solution will work. An alternative solution is to define a measure per definition (having no dimension) in your pie chart. 

MEASURES

adsl + pstn:
Count({<customerid = P({<assetType={'pstn'}>})>*<customerid = P({<assetType={'adsl'}>})>} distinct customerid)

only pstn:
Count({<customerid = P({<assetType={'pstn'}>})>-<customerid = P({<assetType={'adsl'}>})>} distinct customerid)

only adsl: 
Count({<customerid = P({<assetType={'adsl'}>})>-<customerid = P({<assetType={'pstn'}>})>} distinct customerid)

The benefit with @kaanerisen  solution is that you get an dimension that you can select and filter your data. The strenght of the second solution is resource efficiency, with large data sets you will find the SET analysis to calculate faster than the aggr() dimension calculation. 

[tommyl]

Hello all,

thank you for all your responses.

All you replied works if i need the asset count. Here i need a customer labelling. Each customer should have a SINGLE label with following values:

-PSTN: If a customer has only PSTN service(no ADSL service). 

-ADSL: If a customer has only ADSL service(no PSTN service).

-PSTN and ADSL: If a customer both PSTN and ADSL service(have at least one PSTN and at least one ADSL).

Sorry if i couldnt make this more clear before. If you could help me i really appreciate. 

Regards,

[kaanerisen]

Hi Tommyl,

Actually I am pretty lost 🙂 In your post, It seem you want to get number of customers who has pstn, adsl or both and the solutions are accurate for that as you mention.

If you can share a sample data and expected output more clearly, you will get more and accurate solutions indeed 🙂

Regards,

[tommyl]

Hello, 

Sorry for the fuzz.

I cannot provide the data due to NDA. Yet i can share my table outlines like:

Customer:

customerid	name	DoB	segment
cust1	sth1	2010-01-01	SME
cust2	sth2	1990-01-01	SOHO
cust3	sth3	1998-01-01	Corporate

Asset:

customerid	type	....
cust1	pstn
cust1	adsl
cust1	pstn
cust2	adsl
cust2	adsl
cust3	pstn
cust4	pstn
cust5	pstn
cust5	pstn
cust6	adsl
cust6	adsl
cust7	adsl
cust7	pstn
cust7	pstn

There is a requirement about displaying the count of customers as below, according to the assets they have:

-member count who has "ONLY PSTN" = 3 (customers: cust3, cust4, cust5)

-member count who has "ONLY ADSL"=2 (customers: cust2, cust6)

-member count who has "ADSL AND PSTN"=2(customers: cust1, cust7)

I hope i made it clear now 😞

Thank you for your help.

Regards,

[tommyl]

Hello,

Thank you for all your helps, the problem was putting distinct to the measure.

I accept @kaanerisen solution since i set the dimension and measure value the same too, except the "distinct" part.

Thank you all again.