Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Sum( PurchaseSales ) + Sum( {$<UpgradeMonth=PurchaseMonth>} UpgradeSales)
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.
Thanks!
Moshe
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).
Expression: Sum(If(PurchaseMonth = diMonth, Sales)) + Sum(If(UpgradeMonth = diMonth, UpgradeSales))
For more info on setting up date islands, do a search on this site for - you should be able to find several examples that will help you.
Hope that helps
Jonathan
Hi Jonathan,
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?
Moshe
Rather than selecting diMonth from a list box, have you tried using it as a chart dimension?
Jonathan
Jonathan, thanks for your post, specifically noting that set analysis is applied to the whole data set (ie before the dimensions are applied). The Sum(if(field=dimension) alternative is beautiful