topic Re: Calculating Company Size in QlikView

Calculating Company Size

Mon, 08 Feb 2016 10:51:59 GMT

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!

Re: Calculating Company Size

Mark_Little — Mon, 08 Feb 2016 13:35:55 GMT

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

Re: Calculating Company Size

Wed, 10 Feb 2016 08:13:46 GMT

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.