Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
tschullo
Creator III
Creator III

Budget Vs Actuals Graph

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?

1 Solution

Accepted Solutions
Nicole-Smith

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

...

View solution in original post

13 Replies
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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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)

DBCapture.PNG.png

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
Creator III
Creator III
Author

DBCapture2.PNG.png

Nicole-Smith

What fields is %LINK_Key made up of?

tschullo
Creator III
Creator III
Author

Parent Alias Name and PSalesNumber

Nicole-Smith

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

...