Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

meizheng
New Contributor III

Count items group by name based on status

Hi,

I have a table of items with status 'Missing' or 'Installed'. Simplified table below:

NameStatus
AMissing
BInstalled
BInstalled
CMissing
CInstalled

How can I achieve this calculation of total installed and missing items?

NameTotalInstalledMissing
A101
B220
C211

I managed to get the correct numbers but am not able to display it in the way I want to (as above) with the following codes

hostnameTotals:

Load "Host Name",

count(distinct %PWID) as "PW.Total"

Resident tempTable

group by "Host Name";

hostnameInstalled:

Load "Host Name",

if([PW.Status] like 'Installed', count(distinct %PWID)) as "PW.Installed",

    if([PW.Status] like 'Missing', count(distinct %PWID)) as "PW.Missing"

Resident tempTable

group by "Host Name", [PW.Status];

1 Solution

Accepted Solutions
OmarBenSalem
Esteemed Contributor

Re: Count items group by name based on status

Don't alter ur source;

juste in your sheet, create a pivot table:

Capture.PNG

As a dimension: Name

As a measure: count(Status) or count(Distinct Status) ; depends on what u want to show

then add a column : Status

Capture.PNG

Lastly, activate the totals on the Status column:

Capture.PNG

result:

Capture.PNG

7 Replies
OmarBenSalem
Esteemed Contributor

Re: Count items group by name based on status

Don't alter ur source;

juste in your sheet, create a pivot table:

Capture.PNG

As a dimension: Name

As a measure: count(Status) or count(Distinct Status) ; depends on what u want to show

then add a column : Status

Capture.PNG

Lastly, activate the totals on the Status column:

Capture.PNG

result:

Capture.PNG

meizheng
New Contributor III

Re: Count items group by name based on status

was thinking too much and didn't think of using a pivot table haha thank you!!

Best Regards

Serena

meizheng
New Contributor III

Re: Count items group by name based on status

What if I want to calculate Installed/Totals? Can I do this in the pivot table?

OmarBenSalem
Esteemed Contributor

Re: Count items group by name based on status

can you maybe show me with an image how u want to show this?

meizheng
New Contributor III

Re: Count items group by name based on status

Example:

NameTotalsInstalledMissingCompliance %
A1010 (0/1*100)
B220100 (2/2*100)
C21150 (1/2*100)

Compliance % = Installed / Total * 100

OmarBenSalem
Esteemed Contributor

Re: Count items group by name based on status

If u wanted to be exactly as follow:

u'll have to do like this (but then , you won't be able to filter this table by Status...)

So create a pivot table:

as Dimension: Name

As column: =ValueList('Totals','Installed','Missing','Compliance%')

As a measure:

if(ValueList('Totals','Installed','Missing','Compliance%')='Totals', num(count(Status),'# ##0'),

if(ValueList('Totals','Installed','Missing','Compliance%')='Installed', num(count({<Status={'Installed'}>}Status),'# ##0'),

if(ValueList('Totals','Installed','Missing','Compliance%')='Missing', num( count({<Status={'Missing'}>}Status),'# ##0') ,

if(ValueList('Totals','Installed','Missing','Compliance%')='Compliance%', num((count({<Status={'Installed'}>}Status)/Count({<Name=,Status>}Status)),'# ##0,00%')

))))

Result:

Capture.PNG

OmarBenSalem
Esteemed Contributor

Re: Count items group by name based on status

Or you can have sthing like this:

as dimension : Name

as column: Status

as measures:

count( Status)

count(Status)/count(total <Name> Status)

and activate totals on the column Status:

result:

Capture.PNG

Community Browser