Removing 0 from a distinct count of an if statement

[Tupence]

I have a created a report with a table with unique identifier, contact info and daily requests on it for 2006 and on another table the premium service start date which are linked via the unique identifier.

I am trying to do a distinct count of premium users in 2006, i have written an if statement

if isnull (premium service start date) = false then
(unique identifier) else 0

i then distinct count it in the relevant group, however it keeps counting the 0 as a distinct count.

I have tried using a null formula filed and "" without success. any ideas?

 

[synapsevampire]

Change the formula:

if not(isnull(premium service start date)) then
1
else
0

Now do a regular sum of your formula.

-k

 

[Tupence]

Thanks for the suggestion but when the two tables are linked the date appears multiple times as there are numerous daily requests.

the sum just brings back a really high figure.

 

[synapsevampire]

And how would we know that from your first post?

You need to put sometime into your posts, specifically show example data and the expected output.

-k

 

[lbass]

One way of doing this is to open a new formula in the field explorer, name it "null", and then save and close the formula without entering anything in the formula area. Then change your formula to:

if isnull({table.premium service start date}) then date({@null}) else {table.unique identifier}

-LB

 

[lbass]

Oops, that should have been:

if isnull({table.premium service start date}) then
tonumber({@null}) else
{table.unique identifier}

...if the unique identifier is a number. If it is a string, then use:

if isnull({table.premium service start date}) then
{@null} else
{table.unique identifier}

-LB