Calculating Company Size

[#1]

Hello good people here at QlikCommunity.

This forum is great and as a newbie to Qlikview I learn a lot. I could do with help on the following task though:

I have a table with Orders by Date, Status and Company. I now need to achieve the following:

Create a "Company Size" field:

No order in the last 30 days = "No Orders"

Less than 50 orders in the last 30 days = "Small Size Company"

Between 50 and 100 order in the last 30 days = "Medium Size Company"

More than 100 orders in the last 30 days = "Large Size Company"

Only Orders with a "Closed" status should be considered

I will need this new field "Company Size" for other charts, so perhaps it should go into the loading script?

I attach a sample sheet.

Thanks for all your contributions on this forum!

[Mark_Little · Accepted solution]

Hi,

I would try something like the below,

SizeData:

LOAD OrderID,

     Date,

     Status,

     Company

FROM

[..\qlikdata.xlsx]

(ooxml, embedded labels);

NoConcatenate

Temp:

LOAD OrderID,

     Date, 

     Company

Resident SizeData

Where Date >= Today()-30;

CompanySize:

LOAD

  Company,

  COUNT(OrderID)  as OrderCount,

  IF(COUNT(OrderID) = 0,

       'Small Size Company',

       IF(COUNT(OrderID) < 50,

            'Medium Size Company',

            'Large Size Company'

            )

    ) as CompanySize

Resident Temp

Group By Company;

Drop Table Temp;

Mark

[#3]

Hi Mark,

thanks so much for your help, this is great!

I amended the IF statement slightly to get what I want:

CompanySize:

LOAD

  CompanyID,

  COUNT(CaseID)  as OrderCount,

if(COUNT(CaseID) = 0,'No Orders',

    if(COUNT(CaseID) >0 AND COUNT(CaseID) <= 5,'Small',

        if(COUNT(CaseID) >5 AND COUNT(CaseID) <= 20,'Medium','Large')))

as CompanySize

Works like a charm and just what I needed.