Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

evgeniystuchalk
Contributor 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
Contributor II

Re: calculated dimension for amount of deals per contact

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

6 Replies

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?

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
Contributor II

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?

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

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.

evgeniystuchalk
Contributor II

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)

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

evgeniystuchalk
Contributor II

Re: calculated dimension for amount of deals per contact

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
Contributor II

Re: calculated dimension for amount of deals per contact

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

Community Browser