Calculation on Distinct ID List

[mherdt1992]

Hello Guys i have the following problem:

I have a  Data set looking like this:

So i have for every months a list of IDs. If they are activ, S.Termin =0 if inactiv they switch to 1. This condition can switch.

VKange are number of calls and VKbet are sales of Salesman

SP are others for Salesman.

Now i have the following KPIs for VK:

for the distinct list i use : =Count( {$<Month= {''},S.Termin={'0'} > } DISTINCT KNneu   )

for the unique KNange : =Count( {$<Month= {''},S.Termin={0} ,VKange = {">0"}> } DISTINCT KNneu   )

then i want to know KNbet : =count(DISTINCT(if(S.Termin=0 and VKbet>0, KNneu)))

Of course now i what to know, based on the numbers how SP is performing, if SP does the calls and sells.

=Count( {$<Month= {''},S.Termin={0} ,VKbet = {0}, SPbet={">0"}> } DISTINCT KNneu   )

so e.g. i have 6500 unique IDs, 5800 calls VK, 4900 sales. But for SP sales i get 1800 and SP calls 3000, which cannot be, because i dont have as much unique ids ? do i have a logic mistake ? Any suggestions how i can solve this problem ?

[sunny_talwar]

Would you be able to provide some sample data to understand your issue better? also, it would be great if you are able to provide the output you are hoping to see from the sample data provided

[mherdt1992]

Its hard because the data is Very Large and its Sales Data. i hope this helps to understand the problem:

=Count( {$<Month= {''},S.Termin={0} ,  VKange={0},  SPange = {">0"}> } DISTINCT KNneu   ) gives me the value 1 in the second table 5 column.  But the formula says VKange={0}. why its counting 1 ?  the id has been called in month 7, by the salesman. it should be 0, because VKange is 1 in month 7

[sunny_talwar]

VKange is a field or expression here?

[mherdt1992]

Field. I changed to names to make it more clear. This is what my excel looks like.#

S.Termin is a condition status of the ID. 1  means he is not available.

Basicly i what the following KPIs for all IDS S.Termin = 0

IDs with S.Termin  = 0

Calls by Salesman on the distinct list.

Sales by Salesman on the disitnct list.

Calls other, if Calls salesman has 0 calls on the same list.

and sales by other if sales salesman is 0 on the same list.

And Qlik should calculate this based on the months i choose. But my expressions above dont work.

When i choose for example moths 6,7,8 i should get a Distinct List of 6500 IDS, 5000 Calls Salesman, 3000 Sales Salesman, 1000 Calls by other and 500 Sales by Other. It should be like a funnel. Both Calls added cannot be more then 6500. and the Sales of ther can not be higher then 1000. etc..

[sunny_talwar]

I am unable to follow the issue. May be someone else might be able to help here.

Best,
Sunny

[mherdt1992]

yet thank you