sum with date island

[#1]

Hi all,

Í am doing work for a project management assignment. I would like to show the total cost for all projects that are not yet completed.

I would like to show the sum of the total cost for all projects in the coming months (date island) as long as their implementation date (date the project is finished/completed) is before the month of the calander date.

Problem: we have a date island from this forum, but it seems qlikview calculates the sum for each day in the month and not just once per month. (e.g. the amount is 31 times too high).

The dimension is like:

=if(CalendarDate > makedate(2010,11,30),CalendarMonthAndYear)

The expression is:

sum(

if( [Implementation Completion Date] > CalendarDate

, [Current Cum. One off cost 2011-2013]))

Thank you for your help!

[Oleg_Troyansky · Accepted solution]

I think your date Island needs to have one row per Month, to be used in a chart like this - otherwise, the condition is true for each day of the month, and hence the duplication.

Try switching to a monthly Date Island...

[#3]

Hi, Yes that solves the problem. for this time... thanks

But I would like to know how to have a sustainable date island which you can use for all purposes. Maybe that is not possible? Because it is normal masterdata that you have a list of each day. I would think the dimension would loop over each unique value (per month)

[Oleg_Troyansky]

I don't really like the idea of Date Islands - they are too bad for the performance... I realize that this might be the only choice for a Project Management application, with so many different Dates to worry about, and still...I keep thinking that there must be a better way...

So, this of Date Islands as of a "band-aid" solutions. No matter what band-aids you use, the solution is still ugly overall...