Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dynamic sums by month & ID

Hi,

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

PROJECT IDSTART DTEND DTPROJECT INCL DATESHRS PER DAY

1

2/27/123/5/122/27/1210
12/27/123/5/122/28/1210
12/27/123/5/122/29/1210
12/27/123/5/123/1/1210
12/27/123/5/123/2/1210
12/27/123/5/123/3/1210
12/27/123/5/123/4/1210
12/27/123/5/123/5/1210
21/29/122/1/121/29/125
21/29/122/1/121/30/125
21/29/122/1/121/31/125
21/29/122/1/122/1/125
32/15/122/18/122/15/127
32/15/122/18/122/16/127
32/15/122/18/122/17/127
32/15/122/18/122/18/127

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 IDMonthHrs
1Feb 201230
1Mar 201250
2Jan 201215
2Feb 20125
3Feb 201228


Thanks!

S

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

4 Replies
Not applicable
Author

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.

Not applicable
Author

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
Not applicable
Author

What about using AGGR?

see sample attached..

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

Not applicable
Author

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