Distinct Count Formula 1

[dannyb29]

Hi All,

I hope you are well. I am having difficulty in expanding on the formula below. I need to include a distinct count within the formula. Can anyone help?
I need to distinct count the Order ID if the description ="Confirmed on train"

if{OrderStatus.Description}= "Confirmed On Train" then
{Orders.OrderID} else
{@number}

 

[IanWaterman]

Add a running total

Distinct count on {Orders.OrderID}

in evaluate select Formual option and add

{OrderStatus.Description}= "Confirmed On Train"

Ian

 

[lbass]

Or you can change your conditional formula to:

if {OrderStatus.Description}= "Confirmed On Train" then
{Orders.OrderID} else
tonumber({@null})

...where {@null} is a new formula that you open and save without entering anything.

You can then right click on the formula and insert a distinctcount on it.

-LB

 

[dannyb29]

Hi guys,
Thank you for your answers. I have actually done both what you have suggested already as the main section and it works fine. I need the distinct count to be in the formula as I need to use the formula within a crosstab.
I have tried selecting distinctcount as the summerising option within the crosstab expert, but all it does is display each individual Order Id number.

I hope this makes sense.

Dannyb

 

[lbass]

Please explain how you have set up the crosstab in the crosstab expert. What are your row, column, and summary fields, and the type of summary used? A distinctcount of the formula I gave you, used as the summary field, would return the distinct count of Order IDs.

-LB

 

[dannyb29]

Doh! I know what I was doing incorrectly. Really sorry guys.

One other thing if you don't mind helping me with my crosstab now I have the figures sussed.

How do I manipulate the crosstab to my data in colums rather than rows with my formulas.

I will try and give an example.


Nrders No.Confirmed Orders
(formula 1) (formula 2)

Fred Smith 10 3
Karen White 7 2
Mary Kay 12 5


My formulas are as follows:
//Formula one
DistinctCount ({Orders.OrderID}, {Users.LoginName})


//Formula two
if{OrderStatus.Description}= "Confirmed On Train" then
{Orders.OrderID} else
{@number}

Many thanks

 

[lbass]

Go to the customize style tab->summaries->check "horizontal" and "show labels". You can manually edit the labels as necessary.

-LB

 

[dannyb29]

Oh my gosh you are the best!! I did not even know that exisited!!
******THANK YOU****

 

[dannyb29]

Hi Ibass

I didn't want to bother you again, but I'm left with no choice

I am trying to come up with a formula that works out the average of my two formulas shown above. but the distinct count option has to happen first in each formula before the average can take place.

distinct count formula 1 divided by distinc count formula 2.

Dannyb

 

[lbass]

You didn't respond to my earlier request about the crosstab setup--can you confirm that like your sample you do NOT have a column field?

-LB

 

[dannyb29]

Hi Ibass
Yes I caonfirm that I have NO column fields. That's what I was doing wrong before you told me.

D

 

[lbass]

Right click on the {@formula1} summary->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar f1 := currentfieldvalue;
false

Then select the {@formula2} summary->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar f2 := currentfieldvalue;
false

Then create a formula {@0} like this in the field explorer:

whilereadingrecords;
0

Add this as your third summary and then right click on it->format field->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
numbervar f1;
numbervar f2;
totext((f1+f2)/2,1) //1 for 2 decimal

-LB