
Sum of values for different dimension in one dimension
Jochem Jul 22, 2010 3:16 PM (in response to Andreas Buchholz )use set analysis
make a table with dimension "Incoming Order date" and as expression sum(Revenue), as a second expression you have to use set analysis, something like:
=sum({$<Work time from=Incoming Order date>}Presales hours)
hope this can point you in the right direction

Sum of values for different dimension in one dimension
Andreas Buchholz Jul 23, 2010 3:41 PM (in response to Jochem)Unfortunately this is not working. I tried with (result see right table):
sum({$<T_Stunden.VonJahr=IO_Year>} T_Stunden_Presales_Tage)
This screenshot shows the problem. I want to sum up all bookings where presales year = 2010 is selected (see right table). Unfortunately these bookings are not all related to IO year 2010 but also to other IO_years. But I want to show them for the IO_year on the left table so that on the left table 483,4 PD will be shown. Hope this is clear to you.
Any more ideas?

Sum of values for different dimension in one dimension
Ryan Fry Jul 23, 2010 7:14 PM (in response to Andreas Buchholz )I'm not sure I understand your requirement.
Are Revenue and presales hours related by some other dimension? They don't share the same date, you mentioned, but does something else link them?
If so, based on your screen shot below, you don't need to do anything at all. Just sum(T_Stunden_Presales_Tage)

Sum of values for different dimension in one dimension
Andreas Buchholz Jul 24, 2010 7:07 AM (in response to Ryan Fry )Please find attached an extract from my data model.
Total_IO and T_Stunden_Presales_Tage are linked via the project. This means, if I choose "Year_IO" 2010, it sums up the Total_IO correct. But because of the associative analysis, it sums up all Presales_Tage for all projects where the IO_date is 2010as well. But this is not what I want. I want to choose one year (2010) and it should show me sum(Total_IO) for IO_year=2010 and it should show me sum(Presales_Tage) for T_Stunden_vonJahr=2010, means all presales effort bookings for 2010 independent of the project.

Sum of values for different dimension in one dimension
Andreas Buchholz Jul 24, 2010 8:01 AM (in response to Andreas Buchholz )I tried with dynamic dimensions and this might work. At least if I just see 2010. But I don't know the whole concept behind dynamic dimensions. Can somebody give me hints?

Sum of values for different dimension in one dimension
Kevin Littich Jul 25, 2010 6:08 AM (in response to Andreas Buchholz )
ABuchholz wrote:I want to choose one year (2010) and it should show me sum(Total_IO) for IO_year=2010 and it should show me sum(Presales_Tage) for T_Stunden_vonJahr=2010, means all presales effort bookings for 2010 independent of the project.
This expression will do exactly that.
=sum(Total {1<PresalesBookingYear={$(=Only(IO_Year))}>} PresalesValue )
Or using your field names exactly:
=sum(Total {1<T_Stunden_vonJahr={$(=Only(IO_year))}>} Presales_Tage )
Because it is using the 'Only()' function which returns NULL when there is more than one value, you must have a single value selected in IO_year.

Sum of values for different dimension in one dimension
Andreas Buchholz Jul 25, 2010 6:38 AM (in response to Kevin Littich)Amazing, it's working. Thanks much!





Sum of values for different dimension in one dimension
sharond31 Jul 23, 2010 7:26 PM (in response to Jochem)The suggestion Jochem made was a good one. I tried it too.
But, unfortunately it didn't work either....


Sum of values for different dimension in one dimension
Oleg Troyansky Jul 24, 2010 12:16 PM (in response to Andreas Buchholz )Tying together different dates is a common problem, and there are no good solutions to it, that I know of... The "book answer" is to use "Island Date" table, as described in "Developer II" class, but this solution makes the calculations very heavy and is not feasible for large data sets.
Set Analysis can't help here , because SA expression is being evaluated outside of the context of the individual Dimension. For example:
{<Year= {$(vYear)} >} is valid, because the field Year is being compared to a variable vYear, which is not dependent on any chart expressions. At the same time,
{<Year= {OrderYear} >}, where OrderYear is one of the chart dimensions, will not work because OrderYear can be different for each line in the chart, and Set Analysis expression is evaluated outside of the context of the specific cell.
The only "good solution" to the problem of different dates is trying to build a data model where different dates can be combined under the same field "Date". If that's possible,  it would be the best scenario. I know that some data models are way too complex, so this solution is not universal. One should always try though...
hope it makes sense...
If someone has a good solution to the problem of tying different date fields together  please enlighten us.
cheers,