LoginSignup
0
0

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

Last updated at Posted at 2024-05-22

要件/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
image.png

2. To calculate [amount]/[row], I will create a compare table.
image.png

Create 2 formula columns in compare table,
image.png

Set the column header as you want, and input a number, for example '1' in this formula and apply it.
image.png

Create another column with the same method.
image.png

Click [+] to create another calculation column. Click [done] after this.
image.png

3. Put interaction in compare table

Click compare table and click the [advanced Editor] on the right side
image.png

Go to query and find the "Column"
image.png

We are going to use query [Amount] to place value in column "B"
In source query choose query [Amount]
image.png

image.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
image.png

Choose interaction type as Result, and asObject. The interaction will be created automatically, then copy the whole interaction.
image.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

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

4. Calculation result
Now you can try to use filter to get dynamic result.
image.png

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

image.png

image.png

image.png

Reference

[Help Doc]Compare table
[Help Doc]Get Started with Dashboard Interactions

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0