6 Replies Latest reply: Oct 22, 2015 6:51 AM by Evgeny Stuchalkin

# calculated dimension for amount of deals per contact

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:

• ###### Re: calculated dimension for amount of deals per contact

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

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.

• ###### Re: calculated dimension for amount of deals per contact

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:

• ###### Re: calculated dimension for amount of deals per contact

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.

• ###### Re: calculated dimension for amount of deals per contact

Sample is here. I see two possible way solutions:

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

• ###### Re: calculated dimension for amount of deals per contact

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.

• ###### Re: calculated dimension for amount of deals per contact

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

sales_amount:

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

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