To display count of resources filling specific interval of hours in resource category

[ankurakash]

Hi Qlikers,

I have to display - count of resources filling less than specific hours in their specific resource category.

I have attached the sample resource (Excel file).

The excel file contains four fields:

ResourceID, Resource Name, Hours, ResCategory

Here Resource Id is duplicated as the resources have filled hours multiple times in a week.

I was able to get the Resource Id, Total hours against each resource Id and Total Hours for Each ResCategory. However when I try to get data for ResId who have worked less than 15 hours. It's not working for Resource Category.

I have done the following in Pivot table:

Dim: ResourceID

Expression1: Total Hours : Sum(Hours)

Expression2: Cat-A_Hrs: Sum({<ResCategory = {'Cat-A'}>}Hours)          // Working

Expression2: Cat-B_Hrs: Sum({<ResCategory = {'Cat-B'}>}Hours)          // Working

Expression3: Cat-B_LT15Hrs: Sum({<ResCategory = {'Cat-B'}, Hours = {'<15'}>}Hours)          // used single and double quotes for "<15" Not giving me the desired result.

Requesting Qlikers to help.

Regards,

Ankur

[MK_QSL]

Use this..

Sum({<ResCategory = {'Cat-B'}, ResId = {"=SUM(Hours)<15"}>}Hours)

Or

Sum({<ResCategory = {'Cat-B'}, ResId = {"=SUM({<ResCategory = {'Cat-B'}>}Hours)<15"}>}Hours)

[ankurakash]

Thank You Manish. Both expressions are working well. My apology as I did a mistake while explaining my requirement.

Instead of Sum(Hours) it was Count(ResourceID)

and the expressions I wrote were:

I have done the following in Pivot table:

Dim: ResourceID

Expression1: Total Hours : Sum(Hours)

Expression2: Cat-A_Hrs: Count({<ResCategory = {'Cat-A'}>}ResourceID)          // Working

Expression2: Cat-B_Hrs: Count({<ResCategory = {'Cat-B'}>}ResourceID)          // Working

Expression3: Cat-B_LT15Hrs: Count({<ResCategory = {'Cat-B'}, Hours = {'<15'}>}ResourceID)          // used single and double quotes for "<15" Not giving me the desired result.

[MK_QSL]

ResourceID?????

You mean ResId?

Use below expression.

Count({<ResCategory = {'Cat-B'}, ResId = {"=SUM({<ResCategory = {'Cat-B'}>}Hours)>15"}>}ResId)

[ankurakash]

I tried the below query but it is not giving the correct result.

Count({<ResCategory = {'Cat-B'}, ResId = {"=SUM({<ResCategory = {'Cat-B'}>}Hours)>15"}>}ResId)

Regards,

Ankur

[MK_QSL]

According to your sample data, what could be the output you are looking for?

Can you provide that output here?

[ankurakash]

ResId 103 and 104 (in excel sheet) are in Cat-B.

In case of >15 hrs the count(ResId) should be 1 which is ResId:103 having total 18 hrs.

I was looking for <15 hrs where ResId 104 of Cat-B would have displayed as count 1.

[MK_QSL · Accepted solution]

For >15 use this

Count(Distinct {<ResCategory = {'Cat-B'}, ResId = {"=SUM({<ResCategory = {'Cat-B'}>}Hours)>15"}>}ResId)

For <15 use this.

Count(Distinct {<ResCategory = {'Cat-B'}, ResId = {"=SUM({<ResCategory = {'Cat-B'}>}Hours)<15"}>}ResId)

[ankurakash]

Thanks a ton Manish. it works perfect.

Regards,

Ankur

[ankurakash]

I also tried with an alternate method which is working good now: At script level.

based on the attached sample:

1. Loading the normal script as attached qvw

2.  To get overall slab hours:

At script level added the following script:

-----------------------------------------
NoConcatenate
TaskHrs:
Load
Function,
resid,
Sum(HOURS) as hrs1
Resident <MainTable>
Group by
resid,Function;

NoConcatenate
Task_Slab_Hrs:
load
Function,
resid,
hrs1,
     IF((hrs1>=0 and hrs1<=20), '0-20',
      IF((hrs1>=21 and hrs1<=30), '21-30',
       IF((hrs1>30 and hrs1<=48), '31-48',
        IF(hrs1> 48, '48+')                                      
           )
            )
             )as HOURS_SLAB
Resident Task_hrs;

Drop table Task_hrs;

at Design level:

---------------------
piechart:
Dim: HOURS_SLAB
Exp:
=Count({<resid = {"=Sum(hrs1)"}>}resid)

Regards,

Ankur