Compare Orders and Sales

[SriniD]

Working on creating a chart to compare Orders and Sales.

My data table1 has Orders (Year, month), table2 has Sales (Year, month). These are connected to another table3 with a record id.

In my dashboard I am selecting Order Year and Month. I have to calculate Number of orders and Number of sales based on selected Order year and month. I am able to calculate Order summary, but not able to calculate Sales summary properly.

Example of Orders table1

Record Key       Order year     Order month   Order date    Num of orders

1                                2021                   Aug                    08/10/2021              10

3                                2021                 Aug                       08/20/2021              5

Example of Sales table2

Record Key       Sale year     Sale month   Sale date    Num of Sales

1                                2021                   Aug                    08/10/2021              2

4                                2021                  Aug                       08/15/2021              5

table3

Record Key and other columns

1

2

3

4

When I select Order year in Multi select box where I have only Order Year and month. Getting orders total as 15 and Sales total as 2. Since the record # 3 is not part of the table1. How do I overcome this situation, filter out based on the column values rather than record key to sum orders.

Any ideas on this really helpful.

Thank you.

[Saravanan_Desingh]

Try something like this,

Orders:
LOAD *, [Order year] & [Order month] As Key INLINE [
Record Key, Order year, Order month, Order date, Num of orders
1, 2021, Aug, 08/10/2021, 10
3, 2021, Aug, 8/20/2021, 5
];

Sales:
LOAD *, [Sale year] & [Sale month] As Key INLINE [
Record Key, Sale year, Sale month, Sale date, Num of Sales
1, 2021, Aug, 08/10/2021, 2
4, 2021, Aug, 08/15/2021, 5
];

tab3:
LOAD * INLINE [
Record Key
1
2
3
4
];

[Saravanan_Desingh]

Output:

[SriniD]

Thank you Saran7de. The suggestion you have given was simple. I thought about it and works too.

However in my dashboard we have multi select box Year and Month is different filters. When I combine both into common filter and display it will be 60 items to select. Because we are showing last 5 years data. Have plans to show last 7 years data.

I was not able to combine the selected 2 columns in the background and set the filter. Any idea in that way much appreciated.

Thank you
Srini

[SriniD]

Realized with this solution, in case user wants whole year won't be able to select.