Sum of columns..

[Former Member]

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

[MariannevL · Accepted solution]

Hi Priyanka,

You should link two week dimension objects (i'm guessing they come from different data providers).

After that you should be able to display all data in one table say:

Week: 200909 200910 200911 200912 Billing: 1000 3000 4000 5000 Stock: 2000 4000 3000 4000

Notice, for week you can either use Invoice Week or Calweek

Then you can combine the two measures anyway you want to.

Adding, subtracting, dividing. It will be calculated in the context of the week.

Only one more thing to note, use multicube() around your equation as a precaution,

so it will always calculate over both queries.

Hope this helps

[Former Member · Accepted solution]

Hello marianne

These two tables are built upon the same info-providers.

but because of the users requirement we cannot place the Billing and stock in same table.

Also because we have 2 different weeks, Invoice week is for billing data and calweek for stock data

and i want to add billing data of each week with that of stock data.

Could you please how can i get the specified output.

I am new to deski and therefore not aware of multicube.

rgds

Priyanka

[MariannevL · Accepted solution]

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.

[Former Member · Accepted solution]

Yes Marianne,

This is exactly we are doing ..We are having 2 different queries one for Stock data and one for billing data(based upon the same universe).

The problem is arising in combining the weeks i.e stock week and billing week based upon their contents so that i can perform stock data + billing data.

Also i cannot find view data dialogue option that you mentioned in ur last post.

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). 

Thanks and Regards

Priyanka

[Former Member · Accepted solution]

Hello Marianne,

I could find the 'link to' option and linked billing week and invoice week

and could perform the desired calculation.

Thanks very much for helping my problem is solved.

Assigning full points to you

Regards

Priyanka