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: 
Marcoimp
Partner - Creator III
Partner - Creator III

Calculating Clients Turnover

I know there are already many question on it but my question is a little different:

I need ti calculate the client turn over in grid, graph and textboxes:

A "New" client is a client that has NumSpot>0 in 2013 but nothing or 0 in 2012

A "Lost" client is a client that has NumSpot>0 in 2012 but nothing or 0 in 2013

A "Renew" client is a client that has NumSpot>0 in 2013 and  >0 in 2012

The question is: I make my selections (many fields like period, area, client area......) how many clients are new, lost or renew?

I try with something like this:

i.e. for "Lost" clients (but similar for the other values)

=count(          { 

                $<SchedDate_Year={2012}, NumSpot={">0"}>

                +<SchedDate_Year={2013}, NumSpot={0}> 

             }            DISTINCT ClientID)

but, doesn't works (because null values and maybe because a wrong sintax).

Can someone give me a way to get these values?

Thank you very much.

Mark.

M.Imperiale
1 Solution

Accepted Solutions
Gysbert_Wassenaar

Try:

Lost:

=count({$<SchedDate_Year={2012}, NumSpot={">0"}, ClientID*=E({$<SchedDate_Year={2013}, NumSpot={">0"}>}ClientID)>}DISTINCT ClientID)

New:

=count({$<SchedDate_Year={2013}, NumSpot={">0"}, ClientID*=E({$<SchedDate_Year={2012}, NumSpot={">0"}>}ClientID)>}DISTINCT ClientID)

Renew:

=count({$<SchedDate_Year={2012}, NumSpot={">0"}, ClientID*=P({$<SchedDate_Year={2013}, NumSpot={">0"}>}ClientID)>}DISTINCT ClientID)


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
tresesco
MVP
MVP

You are using '+' operator which is for union, but i guess you have to use '*' for AND/intersection logic, because both the conditions have to be true to get your result. Apart from that NULL values have to be treated separately for your 'nothing'.

Marcoimp
Partner - Creator III
Partner - Creator III
Author

Thank you but doesn't works.

I reload data with a script that calculate 0 instead of null for every client (so I dont' have null values anymore)

But it always return 0:

=count({$<SchedDate_Year={2012}, NumSpot={">0"}>*<SchedDate_Year={2013}, NumSpot={0}> }    distinct Cliente)

M.Imperiale
Gysbert_Wassenaar

Try:

Lost:

=count({$<SchedDate_Year={2012}, NumSpot={">0"}, ClientID*=E({$<SchedDate_Year={2013}, NumSpot={">0"}>}ClientID)>}DISTINCT ClientID)

New:

=count({$<SchedDate_Year={2013}, NumSpot={">0"}, ClientID*=E({$<SchedDate_Year={2012}, NumSpot={">0"}>}ClientID)>}DISTINCT ClientID)

Renew:

=count({$<SchedDate_Year={2012}, NumSpot={">0"}, ClientID*=P({$<SchedDate_Year={2013}, NumSpot={">0"}>}ClientID)>}DISTINCT ClientID)


talk is cheap, supply exceeds demand