Budget Vs Actuals Graph

[tschullo]

I am trying to graph my actual sales vs budgeted sales.

I have a table with the budget per customer per month.

I also have a table with my actual sales data with a date from which I created additional columns in that table including month.

I would like to show the YTD budget as a linear graph along with the actuals as line (or bars) in the same chart.

I am able to do this by two charts with one transparent above the other, but was wondering if there was a way to do it in one chart.

The problem is that I have no links between the expressions required in one graph to the other, so I can't use one dimension that covers both, at least not as my data model stands.

Any ideas?

[Nicole-Smith]

If the data is related (same dates, customers, etc.), I would say that the data should be linked or joined in the data model.  Then making your chart would be a whole lot easier too.

[tschullo]

Unfortunately they are not related as  I have a date island in place

[Nicole-Smith]

Can you post a screenshot of your table viewer (CTRL+T)?  Or even better yet, a sample .qvw?

[tschullo]

Chart Dimensions: bMonth (1-12 from budget table) and FPeriod (1-12 from load data)

Actuals Expression:

=count({<FYear={$(vSelectedYear)}, FYbudgetCode={2},

ShipDate={"<=$(=ceil(vCurrDateSelectedYear))"}

>} cnt_key)

Budget Expression:

=sum({<bMonth={"<=$(vCurrPeriod)"}>}  bLoads)

Note that in the two charts at the right I am able to get the data to look right when I split the two tables, but when I merger the tow into one chart with two dimensions, the loads by FPeriod get repeated 12 times per month (and in some cases 13 with no budget data, i.e. 6 | - |  5 | 0)

[Nicole-Smith]

Can you post a screenshot of your table viewer as well (CTRL+T)?  I need to see how your data model looks.

[tschullo]

[Nicole-Smith]

What fields is %LINK_Key made up of?

[tschullo]

Parent Alias Name and PSalesNumber

[Nicole-Smith · Accepted solution]

Can you try adding another field to that %LINK_Key?  Then you should be able to put them on the same chart just using either FPeriod or bMonth as the dimension.

LOAD_INFO_FINAL:

...

[Parent Alias Name] & '|' & PSalesNumber & '|' & FPeriod as %LINK_KEY //or however you create your keys

...

EXIST_CUST_BUDGET:

...

[Parent Alias Name] & '|' & PSalesNumber & '|' & bMonth as %LINK_KEY

...