Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
ankurakash
New Contributor III

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

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

1 Solution

Accepted Solutions
MVP
MVP

Re: To display count of resources filling less than specific hours in resource category

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)

9 Replies
MVP
MVP

Re: To display count of resources filling less than specific hours in resource category

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
New Contributor III

Re: To display count of resources filling less than specific hours in resource category

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.

MVP
MVP

Re: To display count of resources filling less than specific hours in resource category

ResourceID?????

You mean ResId?

Use below expression.

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

ankurakash
New Contributor III

Re: To display count of resources filling less than specific hours in resource category

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

MVP
MVP

Re: To display count of resources filling less than specific hours in resource category

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

Can you provide that output here?

ankurakash
New Contributor III

Re: To display count of resources filling less than specific hours in resource category

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.

MVP
MVP

Re: To display count of resources filling less than specific hours in resource category

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
New Contributor III

Re: To display count of resources filling less than specific hours in resource category

Thanks a ton Manish. it works perfect.

Regards,

Ankur

ankurakash
New Contributor III

Re: To display count of resources filling less than specific hours in resource category

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

Community Browser