I have two tables on the same dataset:
| Table 1 |
|
|
| project |
item id |
cost |
| A |
monitors |
100 |
| A |
keyboards |
50 |
| A |
keyboards |
50 |
| A |
mouse |
20 |
| B |
desk |
200 |
| B |
desk |
200 |
| B |
keyboards 2 |
50 |
| B |
mouse 2 |
20 |
| Table 2 |
|
|
| Project |
member id |
cost |
| A |
1 |
1000 |
| A |
1 |
1000 |
| A |
2 |
2000 |
| A |
3 |
3000 |
| B |
4 |
1000 |
| B |
5 |
2000 |
| B |
5 |
2000 |
| B |
6 |
3000 |
I want to get the ratio of cost per project in table 1 to that of table 2. (total cost for A in table 1/ total cost for A in table 2) However, first I need to duplicate my rows. I’m doing this by getting the cost and using the IDs as follows:
for table 1:
min(cost, [item id])
for table 2:
min(cost, [member id])
once deduplicated, I need to add all the rows per project and then divide them by one another.
I am not sure how the syntax for this LAC would work. Any help?
Thanks!
Do you mean (total cost for A in table 1)/(total cost for A in table 2)?
1 Like
yes, sorry that’s exactly what I meant!
I could be wrong but I don’t think you can use LAC-A here. I would use LAC-W instead.
Table1:
Cost per project = sumOver(cost / countOver({item id}, [{project}, {item id}], PRE_AGG), [project], PRE_AGG)
Table2:
Cost per project = sumOver(cost / countOver({member id}, [{project}, {member id}], PRE_AGG), [project], PRE_AGG)
Then divide the first cost by the second one.
1 Like
The countOver counts how many times each item id or member id appears for the same project. When you divide the cost by countOver, you’re effectively removing the duplicates. For example, you don’t want the cost for project A in table 1 to be 100 + 50 + 50 + 20 because “keyboards” will be counted twice. Dividing by countOver changes the calculation to 100 + 25 + 25 + 20.
1 Like
This worked perfectly. Do you have any resources other than QS documentation that might help with complicated calculations like this?
The only video where I’ve seen this technique used to handle duplicates is this one:
It’s a long video and you’ll have to find where exactly in the video it’s covered. Although it’s an older video, I would highly recommend watching it because it covers more advanced problems. In some cases you can use LAC-A to handle duplicates but I find that LAC-W is still needed sometimes.
Other than that, I’m working on writing up some tips to share with the community. I find that some of the most common questions in the community are related to LAC. I’m hoping the tips will help better understand when to use LAC-A versus LAC-W, and how to handle mismatched aggregation errors.
2 Likes
Thanks for sharing this video (and how the content may be useful to others) @David_Wong!
