Dynamic sums by month & ID

[#1]

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!

S

[#2 · Accepted solution]

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.

[#3]

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

S

[Anonymous]

What about using AGGR?

see sample attached..

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

[#5]

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