Distinct count or specific

[KC3]

Dear Qlikians,

I am trying to make table for count of orders of Facility, means how many orders has been splited to how many FACILITY

I applied  for taken total Count(Sum(Aggr(Count(DISTINCT [Facility_Updated]),[Sale Order Status New],[Display Order Code])))

Which formula should I applied ,

PFB SS for Qlik and Excel for Output data

EXCEL SS-

QlikSS

Thanks

[Michael_Vaisgaard]

Hi,

Could you provide the full dataset? It is difficult to help you from the screenshots you have provided, as they do not contain the fields in your expression.

[KC3]

HI,

I am sharing the excel file i need that split order Calculation Table.

Mainly against  Display Order Code  how many facility we have split. so i need the formula for if Sale Order Status New is Done what is the  distinct count of Display Order Code whose Display Order Code was going under only in 1 facility. and gone for 2 facility, 3 facility like that

For distinct count of Display Order Code i applied  Count(distinct([Display Order Code])) it's shows the count of order i have.

For number of  facility for Display Order Code i applied (Sum(Aggr(Count(DISTINCT [Facility_Updated]),[Sale Order Status New],[Display Order Code])))

Please help me to find how many Display Order Code has been gone for 1 Facility, how many went for2, how many for 3 etc.

Thanks KC

[KC3]

Hi All,

Waiting for the reply 

Please help on it.

[Michael_Vaisgaard]

Hi again,

Here is what I would do:

Create a fact table in the data load:

xlsxdata:
LOAD
date(OrderDay) as OrderDay,
Facility,
DisplayOrderCode,
OrderCount,
SaleOrderStatusNew
FROM [lib://Documents/FacilityCounts.xlsx]
(ooxml, embedded labels, table is Sheet1);

Facts:
load DisplayOrderCode,
count(Facility) as NoFacility
Resident xlsxdata
Group by DisplayOrderCode
;

Then in my table visualization, use set analysis like this:

1:
Count({<
NoFacility={1}
>}DisplayOrderCode)

2:
Count({<
NoFacility={2}
>}DisplayOrderCode)

and so on.

Total is: Column(1)+Column(2)+Column(3)+Column(4)+Column(5)+Column(6)

P.s. I couldn't match your numbers on order count. So I have left that part out.