Accumulation problem

[#1]

I am working on a new project where I am counting up a number of projects that were completed each month during the fiscal year. I want the number of projects each fiscal month to include the counts from the previous month. For example, if I do an expression to count the projects each fiscal month, I may get the following results:

Aug = 2

Sept = 9 with accumulation I would have 11

October = 14 with accumulation I would have 25

My expression looks like this will all the factors I need to consider for a project to count.

COUNT(IF(InYearToDate(Date(Complete_date, 'YYYY-MM-DD') , Today(), 0, 😎 AND MONTH(DATE(Complete_date, 'YYYY-MM-DD')) = 'Aug' AND aggr(Sum(ActualHours), Issue_ID) > 10, Issue_ID))

+

COUNT

(IF(InYearToDate(Date(Complete_date, 'YYYY-MM-DD') , Today(), 0, 😎 AND MONTH(DATE(Complete_date, 'YYYY-MM-DD')) = 'Sep' AND aggr(Sum(ActualHours), Issue_ID) > 10, (Issue_ID)))

I can do the above by selecting the Full Accumulation option on the expressions tab. However, I want to show on my graph a percentage. I have a certain factor that I need to consider for each month but that number is different for each month. For August, I want to divide the accumulated count by 6.83, for September I want to divide the accumulated count by 13.66, and for October I want to divide the accumulated count by 20.49. This is where I get stuck. If I add the division of each month's factor into the expression and then check Full Accumulation, it then accumulates the percentage for each month instead of just the count. In the end, I should be displaying these numbers:

Aug = 29.28%

Sep = 80.53%

Oct = 122.01%

Any suggestions would be greatly appreciated! Thanks.

[Oleg_Troyansky]

wow, those some very complex expressions you've got there!

If you are using ver. 8.5, perhaps you could do it simpler using Set Analysis:

1. Define a detached field holding the months. You can associate your monthly factors with those months. Do not link the project dates to those months.

2. Summarize your projects based on the Date falling in the interval between the beginning of the year and the end of the "detached" month. Something along the following lines:

count( {$<IssueID = {" CompleteDate >= YearStart and CompleteDate <= MonthEndDate "} >} IssueID)

the formula above will give you the accumulated count. Then, just divide the result by the factor:

count( {$<IssueID = {" CompleteDate >= YearStart and CompleteDate <= MonthEndDate "} >} IssueID) / DivisionFactor

good luck!

Oleg

[#3]

How do I create a detatched field. Is that somthing that will have to be done in the script?

[Oleg_Troyansky]

Yes, you load a field that's not linked to your other fields, in the load script.

Oleg

[#5]

Thank you for all your help. I went ahead and created two attached fields in an excel file and loaded them up into Qlikview (these fields are detached from everything else in Qlikview). One field has the names of the months in it and the other has the associated division factors.

This is what my fields look like:

[Detached Months]
Aug
Sep
Oct
Nov
.
.
.

[Division Factors]
6.82
13.66
20.49
.
.
.

I then started with the expression you gave me above as a template and started working from there.

Here is my expression so far.

count ( {$<IssueID = { "Complete_Date >= YearStart('2008-01-01' ,0 ,8 ) AND Complete_Date <= '2009-08-01' "} >} IssueID )

I am not sure how to incorporate my month names in where you have MonthEndDate in your expression:

count( {$<IssueID = {" CompleteDate >= YearStart and CompleteDate <= MonthEndDate "} >} IssueID)

Instead, I just put '2009-08-01' as a ceiling to see if I could get the count to work. If doesn't.

Suggestions?