topic Re: sum of Top 10 weeks in QlikView

sum of Top 10 weeks

Tue, 31 May 2016 08:05:55 GMT

Hi,

I am looking for an expression for the below scenario..

I have employeeName, week, hours information.

now i want to calculate the sum of hours of an employee who exceeds 20 hours a week for more than 10 weeks.

To be more clear...

an employee who spent more than 20 hours for more than 10 weeks, now i need sum of these 10 weeks hours.

kindly help me with the expression ..

Thanks,

Dileep.

Tue, 31 May 2016 10:25:15 GMT

it goes like this...

Sum of hours where hours > 48 for more than 10 weeks.

I need top 10 sum values here..

Its urgent , kindly help me asap.

Gysbert_Wassenaar — Thu, 02 Jun 2016 16:28:18 GMT

Please post a small Qlikview document that illustrates the problem.

miguelbraga — Thu, 02 Jun 2016 16:39:23 GMT

Hi there,

Can you upload a sample of your data? That way we can make a solution for you.

Regards,

Fri, 03 Jun 2016 07:24:00 GMT

Hi Dileep,

I guess this is what you were searching for.

It is working with aggregated values in the data model.

it is using applymap() of weeks per employee to get the "where sum(hours) per week and employee is >=20"

Kushal_Chawda — Fri, 03 Jun 2016 07:32:40 GMT

Assuming Week is Number 1,2,3

=Sum({<Hour={"=Sum({<Week={">=$(=max(Week)-10)"}>}Hour)>20"}>}Hour)

Kushal_Chawda — Fri, 03 Jun 2016 07:34:17 GMT

or better to use Date

Sum({<Hour={"=Sum({<Date={">=$(=max(Date)-70)"}>}Hour)>20"}>}Hour)