Search
Search

# Sum of columns..

Hello BO experts,

I am having 2 tables in a report one table shows the Billing data for each week and other tables shows the stock information for each week.

i.e.

Table 1

Invoice Week: 200909 200910 200911 200912

Billing: 1000 3000 4000 5000

Table 2:

Calweek: 200909 200910 200911 200912

Stock: 2000 4000 3000 4000

I want to calculate the sum of Billing and stock and using following formula for the calculation:

Billing For each<Invoice Week> + Stock For each<Cal Week>

But i am not the correct output,-->

Output which i am getting:

Calweek: 200909 200910 200911 200912

Stock: 2000 4000 3000 400

Total: 3000 5000 4000 5000

i.e. it is along taking the Billing value of only first week for all the calculation

where as i want to add billing of week 1 with the stock of week 1 and billing of week 2 with stock of week 2 and so on.....

Please advice how can i get the correct output and also correct me if formula that i am using is wrong

Regards

Priyanka

##### Former Memberreplied

Hi Priyanka,

Even though it is technically possible to query all in one go, I would split up the query in two parts;

one for the billing (without the stock specific dimensions/measures/conditions), one for the stock data (without the billing specific dimensions/measures/conditions). Very likely in the actual SQL going to the database they are two queries (two select statements) anyhow, just look at the SQL generated in the query panel to verify. What you want to end up with is two dataproviders, each with only one select.

Then link the two weeks objects (in the view data dialogue, you can click on a dimension object from one dataprovider, then click link to and click on the one from the other dataprovider). Even if they are not functionally the same, it will just link/combine based on the the contents, so billing week 200919 will be aligned with the stock week 200919.

Then proceed as I described in my earlier post.

Good luck, its not the simplest thing to start with when you're new to Deski,

Marianne

P.S. multicube() is Deski function that tell it to first merge all concerned cubes (aka data providers) on the common dimensions and only after that start doing any aggregations.

0 View this answer in context
View more on this topic or