cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos

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

ProductAmountFormula 1
1500=IF Amount <=1000 then "A", else "B"
21000=IF Amount <=1000 then "A", else "B"
32000=IF Amount <=1000 then "A", else "B"
43000=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 GroupAmount
A2
B2

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!

Accepted Solutions (0)

Answers (5)

Answers (5)

0 Kudos

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

0 Kudos

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

Former Member
0 Kudos

Hello Avinash,

The user needed results as shown in second table in the original post.

Regards

Niraj

0 Kudos

Hi Niraj

I have attached the screenshot below for the mentioned format

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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