Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ...
Concatenate fields to make a composite key:
Location&Store#&Brand as key
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 ...
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
Concatenate(Sales) //Append records
LOAD
Sales,
Location,
Store#,
Brand,
'Month 1' as Month
FROM
Then create a chart to compare Month 1 vs 2
Month1:
LOAD
Sales as SalesMonth1,
Location&Store#&Brand as key,
Store# as month1_store1,
Brand as month1_brand,
Location as Location1_sales
FROM
Month2:
LOAD
Sales as SalesMonth2,
Location&Store#&Brand as key,
Store# as month2_store1,
Brand as month2_brand,
Location as Location2_sales
FROM