topic Re: Dynamic sums by month & ID in QlikView

Dynamic sums by month & ID

Mon, 05 Mar 2012 20:49:22 GMT

Hi,

Here is a template of the data I am working with:

PROJECT ID	START DT	END DT	PROJECT INCL DATES	HRS PER DAY
1	2/27/12	3/5/12	2/27/12	10
1	2/27/12	3/5/12	2/28/12	10
1	2/27/12	3/5/12	2/29/12	10
1	2/27/12	3/5/12	3/1/12	10
1	2/27/12	3/5/12	3/2/12	10
1	2/27/12	3/5/12	3/3/12	10
1	2/27/12	3/5/12	3/4/12	10
1	2/27/12	3/5/12	3/5/12	10
2	1/29/12	2/1/12	1/29/12	5
2	1/29/12	2/1/12	1/30/12	5
2	1/29/12	2/1/12	1/31/12	5
2	1/29/12	2/1/12	2/1/12	5
3	2/15/12	2/18/12	2/15/12	7
3	2/15/12	2/18/12	2/16/12	7
3	2/15/12	2/18/12	2/17/12	7
3	2/15/12	2/18/12	2/18/12	7

I want to be able to sum the hours by ID & by the month(PROJ INCL DATES) and am unable to get the results. I am only able to get the following:

Jan 2012 10

Feb 2012 5

Mar 2012 7

Can someone help me achieve the following results:

Project ID	Month	Hrs
1	Feb 2012	30
1	Mar 2012	50
2	Jan 2012	15
2	Feb 2012	5
3	Feb 2012	28

Thanks!

Re: Dynamic sums by month & ID

Mon, 05 Mar 2012 21:14:18 GMT

I was able to solve this doing the following, since I only have a snippet, I made an inline chart of the data you provided.

test:

LOAD * INLINE [

PROJECT ID, START DT, END DT, PROJECT INCL DATES, HRS PER DAY

1, 2/27/12, 3/5/12, 2/27/12, 10

1, 2/27/12, 3/5/12, 2/28/12, 10

1, 2/27/12, 3/5/12, 2/29/12, 10

1, 2/27/12, 3/5/12, 3/1/12, 10

1, 2/27/12, 3/5/12, 3/2/12, 10

1, 2/27/12, 3/5/12, 3/3/12, 10

1, 2/27/12, 3/5/12, 3/4/12, 10

1, 2/27/12, 3/5/12, 3/5/12, 10

2, 1/29/12, 2/1/12, 1/29/12, 5

2, 1/29/12, 2/1/12, 1/30/12, 5

2, 1/29/12, 2/1/12, 1/31/12, 5

2, 1/29/12, 2/1/12, 2/1/12, 5

3, 2/15/12, 2/18/12, 2/15/12, 7

3, 2/15/12, 2/18/12, 2/16/12, 7

3, 2/15/12, 2/18/12, 2/17/12, 7

3, 2/15/12, 2/18/12, 2/18/12, 7

]

;

test2:

LOAD *,

month([PROJECT INCL DATES]) as MONTH

Resident test;

Now create a new chart as a straight table with Dimensions: Project ID and MONTH, and Expression titled Hrs

=sum([HRS PER DAY]).

This will get the above table as the result.

I attached a file if you need additional reference.

Hope this is what you were looking for.

Edit: If you want the Month field in the final table to match exactly what you have you actually would want:

month([PROJECT INCL DATES]) & ' ' & year([PROJECT INCL DATES]) as MONTH in your script.

Re: Dynamic sums by month & ID

Mon, 05 Mar 2012 22:42:11 GMT

Thanks for the prompt response bapperson. I used the same logic on this attached qlikview app, but the table doesnt show the right results. What am I missing?

The chart does not display all INCL_MONTHS, although the calculations (when I click on an Activity ID) seem to work fine.

Thanks

Re: Dynamic sums by month & ID

Anonymous — Mon, 05 Mar 2012 23:37:45 GMT

What about using AGGR?

see sample attached..

=AGGR(sum(Hrs*Req%*.5),Activity,INCL_MONTHS)

Re: Dynamic sums by month & ID

Tue, 06 Mar 2012 15:36:45 GMT

Thank you very much for your replies. I used a combination of your suggestions and it seems to work fine. Appreciate your time!