9 Replies Latest reply: Jul 25, 2010 6:38 AM by Andreas Buchholz

Sum of values for different dimension in one dimension

Hi folks,

maybe somebody can help me.

I have two different facts:

1. Revenue

2. Presales hours

Now I want to place both in the same table with a date dimension - to make it simple, let say "year". The problem is, both are related to a date but to different dates. The "Revenue" is linked to "Incoming Order date". The "Presales hours" is linked to "Work time from".

In my table I want to show how much revenue was made per year and also how much presales hours where booked. I'm totally lost because both don't share the same date dimension.

Changing the data model is unfortunately not possible. I thought about a loosely coupled calendar but don't know how exactly this can help me.

So, please give me sime hints.

Thanks,

Andreas

• Sum of values for different dimension in one dimension

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

Unfortunately this is not working. I tried with (result see right table):

sum({\$<T_Stunden.VonJahr=IO_Year>} T_Stunden_Presales_Tage)

http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/1665.SetAnalysis.gif

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

I'm not sure I understand your requirement.

Are Revenue and pre-sales 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

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

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

`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

Amazing, it's working. Thanks much!

• Sum of values for different dimension in one dimension

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

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,