Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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'.
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)
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)