How to use fields from 2 lookups for pie chart?

[gemrose]

Hello Team,

I have 2 look up data and I want to join them through a common field MonthYear. I need to calculate transmission per dept = Total transmission *(size of dept/total size of dept)

In lookup1 I need to calculate the propotion of size based on dept eg;

Transmission for Eng dept = 119 *((100+23)/ 170)

Lookup1:

MonthYear	size	org	dept
July 2022	100	research	Eng
July 2022	23	research	Eng
July 2022	2	data	IU
July 2022	45	research	Lab

Total size

170

Lookup2:

MonthYear	Transmission	ID
July 2022	60	global
July 2022	34	global
July 2022	23	Pbg
July 2022	2	pcf

Total transmission

119

I made a merge of 2 lookup with join using MonthYear but I am able to pass only one token value at a time. I need to get pie chart based on calculated formula for org and sort for top values in dept

Code:

|inputlookup lookup2.csv

|search MonthYear="July 2022"

|join MonthYear

  [|inputlookup lookup1.csv]

 |stats sum(Transmission) as TotalTransmission, sum(size) as Totalsize by MonthYear

 |join MonthYear

 [|inputlookup lookup1.csv

  |search dept="Eng"

  |stats values(MonthYear) as MonthYear,sum(size) as DeptMem by dept]

|eval "Transmission per dept" = round(("Transmission per dept") * (DeptMem/Totalsize),2)

|fields "Transmission per dept"

[gemrose]

Thank you this worked

[yuanliu]

---

I have 2 look up data and I want to join them through a common field MonthYear. I need to calculate transmission per dept = Total transmission *(size of dept/total size of dept)

In lookup1 I need to calculate the propotion of size based on dept eg;

Transmission for Eng dept = 119 *((100+23)/ 170)

---

| inputlookup lookup1.csv
| stats sum(size) as DeptMem by dept
| eventstats sum(DeptMem) as TotalSize
| append
    [inputlookup lookup2.csv
    | stats sum(Transmission) as TotalTransmission]
| eventstats values(TotalTransmission) as TotalTransmission
| eval "transmission per dept" = round(TotalTransmission * DeptMem / Totalsize, 2)
| stats values('transmission per dept') as "transmission per dept" by dept

Note:

• Based on your description, I believe that breakdown by dept is the goal.
• You cannot get total as you illustrated with "by MonthYear".
• You cannot get a pie chart with "by MonthYear" if you want to break down by dept
• Because you need a breakdown by dept, the only useful data in lookup2.csv is a total of Transmission.
• A single append is a lot more efficient than doing joins.