Skip to Content Former Member

# Webi 4.0: Use table block as an reference object for calculation

Hi, experts!

I have two tables.

First table - dynamic table.

Second table - static table.

First table displays data returned by the query (simple query - product dimension and amount measure), and also in that table i define product group based on the amount value. See example below

Product Amount Formula 1 1 500 =IF Amount <=1000 then "A", else "B" 2 1000 =IF Amount <=1000 then "A", else "B" 3 2000 =IF Amount <=1000 then "A", else "B" 4 3000 =IF Amount <=1000 then "A", else "B"

In the second table i need to count how many products in each group. Result should be like this:

Product Group Amount A 2 B 2

I tried to use different formulas: count(product), creating variable which define product group and try to count it. But i always get a wrong because i can't use results of table 1.formula 1 in my second table.

Can i use data that was calculated in another table? Can i use table as an reference object in another table?

If somebody know how to do it, i will be very grateful for your help.

Thank you!

##### Add comment
10|10000 characters needed characters exceeded

### 5 Answers

• Former Member
Apr 01, 2016 at 07:38 AM

Hi,

Try below create variables

1. Grp =If([Amount] ForEach([Product])<=1000;"GA";"GB") In([Product])
2. count =RunningCount([Grp]) In Break
3. Create table with this Product,Grp, count
4. then apply break on Grp
5. Then delete the product column
6. Remove breakfooter ,header from manage break sections
7. Right click the table and select the option display table header
##### Add comment
10|10000 characters needed characters exceeded
• Former Member
Apr 01, 2016 at 07:50 AM

Hi,

Simply put two columns in the block:

Column 1 Amount

=If ([Amount] <=1000) then "A" else "B" =Count([Product)]

Check if this helps.

Regards,

Yuvraj

##### Add comment
10|10000 characters needed characters exceeded
• Former Member
Apr 01, 2016 at 09:41 AM

Hi Alex,

The first table is simple and will be same as what you have shown in the screen shot for the second table please follow the below steps.

1 create a new formula using your first tables formula defined in column Formula 1

[Formula1]=IF Amount <=1000 then "A", else "B"

2. Now drag this formula to the report you will get A and B as two different rows.

3. Insert a blank column to the right of this where we will display the count and define formula there as

=Count([Formula1] In ([Product]))

You will get the result that you want. Let me know if this helps.

Regards

Niraj

##### Add comment
10|10000 characters needed characters exceeded
• Apr 01, 2016 at 09:43 AM

Hi Alexandra

I took similar scenario as yours and i am able to display data in same format as suggested in screenshots.

Please refer below screen shots for confirmation  Capture.PNG (142.0 kB)
3.PNG (125.8 kB)
##### Add comment
10|10000 characters needed characters exceeded
• avinash pandey Former Member

Hi Niraj

I have attached the screenshot below for the mentioned format  Capture.PNG (112.0 kB)
Capture.PNG (142.5 kB)
• Apr 01, 2016 at 10:17 AM

Hi Alexandra

Please follow below steps for getting the results in desired format

1. Check the syntax of formula in first table .Editor is reading as text .It should be like this

=If [Amount] <=1000 Then "A" Else "B"(paste in the editor)

2.For getting the group member count use

=count([Product])(paste in the editor)

I got the result in desired format using above details. I have already attached screenshots in earlier post regarding this question. Let me know in case of any clarification.

Regards

Avinash Pandey

##### Add comment
10|10000 characters needed characters exceeded