comparing columns with the same values from two different tables

[Anonymous]

Hi! Thank you so much for taking the time to read my question and potentially help me!

I have to create a dashboard that takes data from two different tables (two different months) and compare the respective sales where three columns match.

Brand:      Location:          Store#:          Sales:

441         XX                    1                    2500

640          YY                    1                     3000

560          ZZ                    2                    2000

901          YY                  3                    400

678         CC                  3                    5000

So I have two tables like the one above (one for month1 and another for month2), and I need to see where the sales increased by 10% between the two months where the region, brand, and store# are the same. (ie, I am trying to compare the brands, seeing if any brand started selling more than I expected them to, and therefore need the store# and location to be the same).  I have attached a QVW document as well, if that would help, but I am truly at loss of how to better do this.

I have read about using the key_ in the load script, but in a past time of trying to do this, it ended up increasing the sales number so I am very wary of this ...

[aarkay29]

Concatenate fields to make a composite key:

Location&Store#&Brand as key

[Anonymous]

Thank you Aar!

So if my load script is the following, what would I do to that? How would I rewrite it?

LOAD Location as month1_location,
     Store# as month1_store1,
     Brand as month1_brand,
     Sales as month1_sales,
     Location as key_location,
     Store# as key_store1,
     Brand as key_brand
FROM ...;

LOAD Location as month2_location,
     Store# as month2_store1,
     Brand as month2_brand,
     Sales as month2_sales,
    Location as key_location,
     Store# as key_store1,
     Brand as key_brand
FROM ...

[albertovarela]

As a stating point here's what I'd recommend:

Load your data in a single fact table:

Sales:

LOAD

     Sales,

     Location,

     Store#,

     Brand,

     'Month 2' as Month

FROM (ooxml, embedded labels);

Concatenate(Sales) //Append records

LOAD

     Sales,

     Location,

     Store#,

     Brand,

     'Month 1' as Month

FROM (ooxml, embedded labels);

Then create a chart to compare Month 1 vs 2

[aarkay29]

Month1:

LOAD

     Sales as SalesMonth1,

      Location&Store#&Brand as key,

     Store# as month1_store1,

     Brand as month1_brand,

     Location as Location1_sales

FROM (ooxml, embedded labels);

Month2:

LOAD

     Sales as SalesMonth2,

     Location&Store#&Brand as key,

     Store# as month2_store1,

     Brand as month2_brand,

     Location as Location2_sales

FROM (ooxml, embedded labels);