I have a table for purchases and a table for upgrades. Both tables have their own date fields like this: PurchaseMonth and UpgradeMonth. What I would like to do is show a graph that sums both Purchases and Upgrades across months. For the dimensions field i'm using PurchaseMonth. Here's what I have for the expression:
The problem i'm having is the UpgradeSales aren't getting added into the chart just the Purchase sales. I believe this is because I am using PurchaseMonth as the dimension. I've been messing around with the set analysis with no luck. I'm thinking I might need to create some sort of month table that isn't linked to either sales or purchases then graph against that value. Not sure how to do that though.
The problem is that the set analysis is applied to the whole data set (ie before the dimensions are applied). So the second part of your expression is looking for upgrades in the same month as the purchase.
I think your best bet is to use a date island, and link sales & upgrades with Sum..if statements, like this...
Dimension: the month from the date island (eg diMonth).
Thanks for replying. I have tried your solution and i do get good numbers. My only problem is it wont display all months at one time. I created a diMonth list box and the chart only displays anything when a selected a particular month in this listbox. Any ideas?