on 04-01-2016 7:09 AM
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!
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Try below create variables
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.