I have one sheet which is created manually as planned expenses and actual data is fetching from Oracle fusion. I want a table for wach quarter or month to compare actual vs planned. Example as below. Test Two tables.xlsx Planned table. Vendor Planned Quarter Segment A 100 Q1 X A 150 Q1 Y A 100 Q1 Z B 100 Q1 X B 175 Q1 Y C 175 Q1 X C 250 Q1 Y C 150 Q1 Z D 125 Q1 Z A 100 Q2 Y A 150 Q2 Z B 100 Q2 X B 100 Q2 Y B 175 Q2 Z C 175 Q2 X C 250 Q2 Z D 150 Q2 Y D 125 Q2 Z Actual Table: ID Sub ID Vendor Paid Total Paid Quarter Segment 1 1.1 A 75 95 Q1 X 1 1.2 A 20 95 Q1 X 2 2.1 A 155 155 Q1 Y 3 3.1 A 50 110 Q1 Z 3 3.2 A 20 110 Q1 Z 3 3.3 A 40 110 Q1 Z 4 4.1 D 175 155 Q1 Z 4 4.2 D -20 155 Q1 Z 5 5.1 A 100 100 Q2 X 9 9.1 E 120 95 Q2 Z 6 6.1 A 155 155 Q2 Y 7 7.1 A 50 110 Q2 Z 7 7.2 A 20 110 Q2 Z 7 7.3 A 40 110 Q2 Z 8 8.1 D 155 155 Q2 Z 8 8.2 D 0 155 Q2 Z Final Table: Vendor Q1 Q2 Planned Total Paid Planned Total Paid A 350 360 250 365 B 275 375 C 575 425 D 125 155 275 155 E 95 Grand Total 1325 515 1325 615

Oracle Communities

Oracle Analytics and AI

Child Item

How to build a one table to show data from two different datasets in workbook.

Rajakumar Burra

I have one sheet which is created manually as planned expenses and actual data is fetching from Oracle fusion.

I want a table for wach quarter or month to compare actual vs planned.

Example as below.

Test Two tables.xlsx

Planned table.

Vendor	Planned	Quarter	Segment
A	100	Q1	X
A	150	Q1	Y
A	100	Q1	Z
B	100	Q1	X
B	175	Q1	Y
C	175	Q1	X
C	250	Q1	Y
C	150	Q1	Z
D	125	Q1	Z
A	100	Q2	Y
A	150	Q2	Z
B	100	Q2	X
B	100	Q2	Y
B	175	Q2	Z
C	175	Q2	X
C	250	Q2	Z
D	150	Q2	Y
D	125	Q2	Z

Actual Table:

ID	Sub ID	Vendor	Paid	Total Paid	Quarter	Segment


1	1.1	A	75	95	Q1	X

1	1.2	A	20	95	Q1	X

2	2.1	A	155	155	Q1	Y

3	3.1	A	50	110	Q1	Z

3	3.2	A	20	110	Q1	Z

3	3.3	A	40	110	Q1	Z

4	4.1	D	175	155	Q1	Z

4	4.2	D	-20	155	Q1	Z

5	5.1	A	100	100	Q2	X

9	9.1	E	120	95	Q2	Z

6	6.1	A	155	155	Q2	Y

7	7.1	A	50	110	Q2	Z

7	7.2	A	20	110	Q2	Z

7	7.3	A	40	110	Q2	Z

8	8.1	D	155	155	Q2	Z

8	8.2	D	0	155	Q2	Z

Final Table:

Vendor	Q1		Q2
	Planned	Total Paid	Planned	Total Paid
A	350	360	250	365
B	275		375
C	575		425
D	125	155	275	155
E				95
Grand Total	1325	515	1325	615

Find more posts tagged with

Accepted answers

Chere-Oracle

Hi @Rajakumar Burra ,

I recommend you create calculations for Vendor and Quarter using the IFNULL() function, like this:

Then you can use this calculated Vendor and Quarter in your pivot table.

All comments

Chere-Oracle

You can join the two datasets by Vendor and Quarter. Or you can create a dataset that includes both tables—the manual table and the one fetched from Oracle Fusion — joined by Vendor and Quarter. This pre-joined dataset would, in a workbook, display two tables.

Rajakumar Burra

@Chere-Oracle Please can you create a DV with attached spread sheet data and final results like shown in Final sheet in spread sheet.

Highly appreciated your help. I want this without Dataflow. I am fine with datasets join in Workbook or join two in Dataset.

Chere-Oracle

Hi Rajakmar,

I tried it, and there is a problem. The join between the two tables — when joined on common columns Vendor, Quarter, and Segment — gives multiplied Planned values due to the Actuals break-down by Sub ID. It would be best to join Planned with an aggregated Actuals table, grouped by Vendor, Quarter, and Segment (so that it has only 1 row per group). Otherwise, it would take some finagling to get rid of the multiplication.

If you cannot get the aggregated Actuals table for some reason, you could add a filter in the dataset's table definition, to filter the rows to only the Sub IDs that have .1 (I mean, a 1 in the first decimal position), and then use the Total Paid column in your pivot, instead of the Paid column.

PS: I noticed there is an error in the Total Paid totals. They don't all match the sub totals of the Paid column.

Rajakumar Burra

Thanks @Chere-Oracle Still I am unable to get what I want without Dataflow.

I can get the totals accurate using aggregates but missing Vendors (B,C) and adding empty quarter.

Attached the Workbook. Please let me know if I am missing something.

Test Tables.zip

Chere-Oracle

Hi @Rajakumar Burra ,

I recommend you create calculations for Vendor and Quarter using the IFNULL() function, like this:

Then you can use this calculated Vendor and Quarter in your pivot table.