Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
evgeniystuchalk
Partner - Creator II
Partner - Creator II

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:

1 Solution

Accepted Solutions
evgeniystuchalk
Partner - Creator II
Partner - Creator II
Author

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:

stuff2.png

View solution in original post

6 Replies
robert_mika
Master III
Master III

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
Partner - Creator II
Partner - Creator II
Author

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?

N6TMADGDQEiaKFZgEZCp3A.png

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:

goal.png

robert_mika
Master III
Master III

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
Partner - Creator II
Partner - Creator II
Author

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
Partner - Creator II
Partner - Creator II
Author

Mean while, partial solution founded.

stuff.png

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
Partner - Creator II
Partner - Creator II
Author

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:

stuff2.png