Chart by month showing sums from different tables

[#1]

Hello,

I have two complementary tables, one for sales orders to customers and one for buying orders from suppliers.

I wish to compare month by month the sum of price orders of both the entities. What do I have to use as dimension in this case? If I create a new calculated field as year+month from sales date field and use it as dimension then sum sales are ok, but not buying. And viceversa.

Thanks for any reply.

Regards.

--

Marco Pikiri

[swuehl]

Please have a look at Henric's blog:

Canonical Date

[#3]

Hi Marco

If you have two tables that have the same kind of information in them and you just want to compare sales against purchases you can use the following.

Let us say, as in your example, that you have sales data and purchases data and for each of these you have data that includes an action date, a name and an amount.  In this case you actually have the same type of data for suppliers and customers i.e. the date, the amount and a name for the supplier/customer.

If you first load the sales data then load the purchases data, labelling the name and amount fields to show which type of data you are loading, you can then pick out which type i.e. sales or purchases you wish to sum.

As an example here is a simple script you could use.

Sales:
LOAD
     Date(ActionDate,'MMM-YYYY') as MonthYear,
     Customer,  //Customer Name
    SalesAmount 
FROM
[SalesvPurchases.xlsx]
(ooxml, embedded labels, table is Sales);

Purchases:
Load
     Date(ActionDate,'MMM-YYYY') as MonthYear,
     Supplier,  //Supplier Name
    PurchaseAmount
FROM
[SalesvPurchases.xlsx]
(ooxml, embedded labels, table is Purchases);

Here I have loaded from an Excel file where sales are on one worksheet and purchases are on another.

Now to get the sales to show on one group of bars and the purchases to show on another you can use expressions to pick out which data you want to display.

For the sales expression use: Sum (SalesAmount)

For the purchases expression use: Sum (PurchaseAmount)

For the dimension use: MonthYear

The result will be this:

which is I think what you are trying to achieve.

When you select one or more MonthYear values in a list box you will actually be selecting both the sales and the purchases transacted in that period.

If you wanted you could then create a third result set of sales-purchases to give a net result for each month.

E.G.

In the above I have coloured the bar green if the net result is above 0 and red if it is below 0.

If it is possible (and it should be assumed that it is) that you may have months where you have no sales or no purchase then it is advisable to create a master calendar to create a value for every period between your minimum and maximum date.  This will avoid any missing months in your chart.  Using the above example your new load script would then  become:

Sales:
LOAD
ActionDate as SalesDate,
Date(ActionDate,'MMM-YYYY') as MonthYear,
Name as Customer,  //Customer Name
   Amount as SalesAmount 
FROM
[SalesvPurchases.xlsx]
(ooxml, embedded labels, table is Sales);

Purchases:
Load
ActionDate as PurchaseDate,
Date(ActionDate,'MMM-YYYY') as MonthYear,
Name as Supplier,  //Supplier Name
   Amount as PurchaseAmount
FROM
[SalesvPurchases.xlsx]
(ooxml, embedded labels, table is Purchases);


tempDates:
Load distinct
SalesDate as dateTemp
Resident Sales;
Load distinct
PurchaseDate as dateTemp
Resident Purchases;


DateRange: 
Load 
min(dateTemp) as minDate, 
max(dateTemp) as maxDate 
Resident tempDates; 

Let varMinDate = Num(Peek('minDate', 0, 'DateRange')); 
Let varMaxDate = Num(Peek('maxDate', 0, 'DateRange')); 
DROP Table DateRange;


Trace $(varMinDate);
Trace $(varMaxDate);

TempCalendar: 
LOAD 
$(varMinDate) + Iterno()-1 As Num, 
Date($(varMinDate) + IterNo() - 1) as TempDate 
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 
Load  
TempDate AS Date, 
Week(TempDate) As Week, 
Year(TempDate) As Year, 
Month(TempDate) As Month,
Date(MonthStart(TempDate),'MMM-YYYY') as MonthYear, 
Day(TempDate) As Day, 
WeekDay(TempDate) as WeekDay 
Resident TempCalendar 
Order By TempDate ASC; 
Drop Tables tempDates,TempCalendar; 

Hope that helps

Kind regards

Steve

[ankitbisht01]

Steps to be followed

1. Concatenate these two tables in order to make master calendar from them.

Sales:
LOAD
     Date(SalesDate,'MMM-YYYY') as MonthYear,
     Customer, 
    Amount as Sales_amount,

FROM
Sales_Table;
concatenate
Purchases:
Load

     Date(PurchaseDate,'MMM-YYYY') as MonthYear,
     Supplier, 
    Amount  as Purchase_amount
FROM

Purchases_Table;

2. now make a Bar chart.

Dimension- MonthYear

Expression1-  Sum(Sales_amount);

Expression 2- Sum(Purchase_amount)

[#5]

Doing this I'm afraid I will have circular reference.

A detail I didn't mention is that at now the two tables share a third table containing a field in common, used in file as first main selection to properly filter their records. Now I can't verify this, just next monday at work, but am I wrong if I say that linking them with a master calendar I will have a circular reference? If so, what could be an alternative?

--

Marco Pikiri

[ankitbisht01]

ok , but what are the fields are in there in that table , how these fields are connect to these two tables , this is the question , on the base of that problem will be handled, may be that tables is also a fact table can concatenated with these tables , and we can make a master calendar differently.

[#7]

Hello ankit,

following is the scenario that I've mostly simplified:

Project is the main entity from which the final user starts his investigation. He wants to compare monthly total amounts of sales and purchases referring to a specific project: now connecting purchases and sales table with master calendar I'm going to get circular reference...

Thank you all for your answers, in the meantime I'm going to study references mentioned by swuehl and others and trying to apply them.

Regards.

--

Marco Pikiri

[ankitbisht01]

Hi,

There are two tables " purchase " and "sales" these two tables are fact tables , you can concatenate them , and can get min(date) and max(date) make your master calendar on the base of that.

Where as "Projects" is a Dimension Table. so your master calendar and Dimension table will be linked to fact table  and you will make a star schema. then apply your expression on charts

Thanks

[#9]

Hello,

assuming I concatenate them on a new linked table "SalesPurchasesLink" as you recommend, then will I can still select a set of sales orders from original "Sales" table and see the relative amount refreshed on chart? In this case these entities are reclining on different "arms" of my star schema, right?

Regards.

--

Marco Pikiri

[ankitbisht01]

yup definitely, you will not be having any problem doing so because for purchase you are having a  OAR amount field, and for Sale you have OVR_amount , you just need to take sum of these to fields in expression. try it