calculated dimension for amount of deals per contact

[evgeniystuchalk]

Hi every one! I have two tables: Contacts and Deals, that connected by kei field "Contact ID".

I can calculate amount of deals for each contact: 0, 1, 2 e.t.c

What i need to do for using amount of deals as dimension?

      | Total contacts| 2 sales | 1 sale | 0 sales

sep |      200          |     100   |   50    | 100

oct  |      300          |     50    |    100  | 150

Details in this video:

[robert_mika]

Do you need to know how many contacts or how many deals has been created per month?

Because your video says so.

You have got more contacts that deals does this mean than not all contacts have deals?

How does your data model look like?

If deals are connect with contacts you can use just count deals per month to get your result.

It would be better if you attached your file so we can see your data.

BTW.I like your way of creating question.

[evgeniystuchalk]

BTW.I like your way of creating question.

Thanks) While i'm not super-pro at Qlik, I'm afraid to miss something important. And prefer describe situation by this way.

How does your data model look like?

Contact ID (ID контакта) - key field

You have got more contacts that deals does this mean than not all contacts have deals?

Yes

Do you need to know how many contacts or how many deals has been created per month?

I knew it already. count([Contact ID]) for first, and count ([Deals ID]) for second. I want to know, how many contacts, that was created per month, have:

1) zero deals

2) one deal

3) more then one deal

There how i see desired outcome:

[robert_mika]

Could you post sample of your data or application.

I assume you need to create 3 expression for each case, but it would be better to see your data.

[evgeniystuchalk]

Sample is here. I see two possible way solutions:

1) create calculated dimension in app construction mode (id don't know how)

2) create additional dimension at moment of data loading (still don't know how)

[evgeniystuchalk]

Mean while, partial solution founded.

I can count amoun of contacts, thet have any deals, with this formula:

Count( {$<[Contact ID]=-{} >} [Deal ID])

It counts all contacts, that have not empty Deal ID. So i see amount of contacts, that have non zero sales.

But main question still open: how count amount of contacts with one deal, two deals, three deals etc.

[evgeniystuchalk · Accepted solution]

Solved completly, by creating addiotional table and calculating this dimension.

Code of additional table:

sales_amount:

load

[ID контакта],

count(distinct([ID сделки])) as [Кол-во покупок]

resident leads

Group by [ID контакта];

Full code: