Calculation based on 2 queries(2つのクエリに基づく計算) in CRM Analytics

#### 要件/Requirements
Sometimes, you create two queries in a dashboard. These may come from different datasets, or be filtered by different criteria. However, you might want to use these two queries for some calculations.

This blog will explain how to do this in CRM Analytics."

Now, here's the Japanese translation:

時々、ダッシュボードで2つのクエリを作成します。これらは異なるデータセットから来ているか、異なる条件でフィルターされているかもしれません。しかし、これら2つのクエリを使って計算を行いたい場合があります。

このブログでは、CRMアナリティクスでこれを行う方法を説明します。


#### 手順/Manual
**1. Create 2 queries**
     For example, I created 2 queries,one has been named as [Amount], and another has been named as [row]. The [Amount] will be effected by 3 filters in dashboard, but the [row] will be only effected by 2 filters. Filter [Forecast category] has been excluded in [row] as following:
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3672646/915cf23a-b9a3-b32a-aa70-19167b544fca.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3672646/ec39921c-2e06-e11b-b663-fb4989a9c574.png)

**2. To calculate [amount]/[row], I will create a compare table.**
 ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3672646/0e3a9962-a724-a09a-86c8-593005063928.png)

 Create 2 formula columns in compare table,
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3672646/c573527b-5cee-e8c3-5c5b-91c01182efdd.png)

Set the column header as you want, and input a number, for example '1' in this formula and apply it.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3672646/703cab48-b8b5-d319-f2b4-f59352262580.png)

Create another column with the same method. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3672646/8307784b-c2bb-adfd-c2a1-5a4547a491f6.png)

Click [+] to create another calculation column. Click [done] after this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3672646/a87eb4ee-8b1f-f93e-9e3a-66c4d47c6b37.png)


**3. Put interaction in compare table**

Click compare table and click the [advanced Editor] on the right side
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3672646/d8fd0f62-4c2d-9586-3a8b-a0558b7afb32.png)

Go to query and find the "Column"
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3672646/2ebf617a-e0a8-65b8-b8a8-a41c38a7c69e.png)

We are going to use query [Amount] to place value in column "B"
In source query choose query [Amount] 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3672646/ebb3cccf-77a1-bc45-1956-2f7257aa97b4.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3672646/3637e5ec-45ea-fd75-045c-a4197850f9ab.png)

In source data, chose Column and the column you want to use. On the right down side, you can check the preview.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3672646/8f2692b1-6275-7775-709b-3d3de12a83fa.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3672646/2506f973-f402-f92f-356d-1a70a1bdc533.png)

Choose interaction type as Result, and asObject. The interaction will be created automatically, then copy the whole interaction.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3672646/c239b559-be44-0aa3-18fb-df9ed757aa51.png)

Put the copied interaction in the formula of column "B"
as {{column(lens_1.result, [\"A\"]).asObject()}}/1

Don't forget to input "**/1**" in above interaction. Without "**/1**", you will get error.

 ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3672646/78e95c14-9e31-fa48-e944-941e0d3377cb.png)


You can use source query [row] to replace the formula of column C by the same method. Here I will skip the steps and show the result.

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3672646/8b356243-d3f8-9726-2bff-ac45b09be704.png)


**4. Calculation result**
Now you can try to use filter to get dynamic result.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3672646/be57da3e-04ba-bfe5-3a3e-fd7467cf5462.png)

Of course, you can also show the result of column in text widget or number widget.

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3672646/56ba525e-c4f3-e3bb-6b8d-bcc7289fd4ab.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3672646/2902b3d8-44cc-61ab-7a3c-5829e1525589.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3672646/c921aec4-f90a-a5b1-f4f8-9ec18ecbd7fa.png)

#### Reference
[Help Doc][Compare table](https://help.salesforce.com/s/articleView?id=sf.bi_explorer_compare_table_nav.htm&type=5)
[Help Doc][Get Started with Dashboard Interactions](https://developer.salesforce.com/docs/analytics/bi-dev-guide-bindings/guide/bi-dbjson-bindings.html)