topic formula with count in QlikView

formula with count

Wed, 27 Jul 2011 11:43:08 GMT

Hi,

I need HELP!

What I am trying is to count the On-Time and Late Shipment.

I wrote on my script these:

ShipDate-DueDate as DayTimeShiping,

if(DayTimeShiping <= 0, 'OnTime') AS OnTime,

if(DayTimeShiping = 1, '1 Day Late') AS OneDayLate,

if(DayTimeShiping = 2, '2 Days Late') AS TwoDaysLate,

if(DayTimeShiping = 3, '3 Days Late') AS ThreeDaysLate,

if(DayTimeShiping > 3, '3 Days or Later') AS ThreeDaysLateOrMore

And then I have a Diagram:

with SalesPerson and I have a formula--> for example: count (OneDayLate) etc

but it dosn't count correct, I don't know why? I have added a pic of that...

Can someone tell me why it dosn't count correct, please?

formula with count

Miguel_Angel_Baeyens — Wed, 27 Jul 2011 12:00:10 GMT

Hi,

Field "OneDayLate" has the same value in all cases. Count() returns the number of occurences of a value in a field, irrespective of they are duplicated or not, so you probably need to add some criteria to. Are tables linked? Are you selecting the dates or Shipment Nos?

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

formula with count

Wed, 27 Jul 2011 12:19:00 GMT

What you mean to linked tables? I am selecting The number of Shipment, which has Delay or the shipments were On Time.

The Output should shows how many time has the shipment the Delay or On-Time.

But I don't understand, how QlikView is working. If you see my added pic, you can see two tables. The first table I worked with the count-formula, and the output there is 47, which is wrong. The second tables shows just two times (that one is correct). I thought QlikView will count the two time the 'value'...

Re: formula with count

Miguel_Angel_Baeyens — Wed, 27 Jul 2011 17:40:10 GMT

Hi,

What happens if you use instead

Count({< OneDayLate = {'1 Day Late'} >} ShipmentID)

I meant if the table where the SalesPerson field is is linked to the Shipments table that you show in your script.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

formula with count

Thu, 28 Jul 2011 07:24:11 GMT

Hi,

No the tables are not linked. The SalesPerson are from "Employee Table" and The Shipment are in SalesOrder Table.

I tried:
Count({< OneDayLate = {'1 Day Late'} >} SalesPerson)

and then this: Count({< OneDayLate = {'1 Day Late'} >} EmployeeEntityID)

Its not working

formula with count

Thu, 28 Jul 2011 09:17:09 GMT

I did a Mistake! I should count the SalesOrderID, and then it shows correct! As Dimension I took the ShipmentStatus...
Miguel Thanks for your help and for spending time for that!

Regards...