topic Re: Budget Vs Actuals Graph in QlikView

Budget Vs Actuals Graph

tschullo — Wed, 22 Jan 2014 15:13:05 GMT

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?

Re: Budget Vs Actuals Graph

Nicole-Smith — Wed, 22 Jan 2014 15:19:34 GMT

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.

Re: Budget Vs Actuals Graph

tschullo — Wed, 22 Jan 2014 15:23:36 GMT

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

Re: Budget Vs Actuals Graph

Nicole-Smith — Wed, 22 Jan 2014 15:25:24 GMT

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

Re: Budget Vs Actuals Graph

tschullo — Wed, 22 Jan 2014 16:29:29 GMT

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)

Re: Budget Vs Actuals Graph

Nicole-Smith — Wed, 22 Jan 2014 16:36:02 GMT

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

Re: Budget Vs Actuals Graph

tschullo — Wed, 22 Jan 2014 16:43:16 GMT

Re: Budget Vs Actuals Graph

Nicole-Smith — Wed, 22 Jan 2014 16:54:17 GMT

What fields is %LINK_Key made up of?

Re: Budget Vs Actuals Graph

tschullo — Wed, 22 Jan 2014 17:05:13 GMT

Parent Alias Name and PSalesNumber

Re: Re: Budget Vs Actuals Graph

Nicole-Smith — Wed, 22 Jan 2014 17:57:17 GMT

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

...

Re: Budget Vs Actuals Graph

tschullo — Wed, 22 Jan 2014 19:47:28 GMT

Thanks, that worked. Now I just need to replace my numbers with a month abbreviation in the axis. I have that in a Month field with 1=Apr, etc, should I just add Month to my budget Table and use it as a dimension rather than Period?

Re: Budget Vs Actuals Graph

Nicole-Smith — Wed, 22 Jan 2014 20:03:17 GMT

Yup, that should work since the tables are linked properly now.

Re: Budget Vs Actuals Graph

tschullo — Wed, 22 Jan 2014 20:15:06 GMT

Thanks for your help I really appreciate it!

Re: Budget Vs Actuals Graph

Fri, 13 Jun 2014 12:03:20 GMT

Hello everyone,

I like to analyse the Target and Current Revenue.Below is the scenario,

Current Revenue per month- 80,000

My target -1,00,000

Travelling Expenses-10,000

Electricity Charges -3000

Other Expenses -1000.

My Questions is that:

I have a Gauge Chart where One end will display the Current Revenue and opposite will display the Target which I want to achieve.i have three slider control in which Travelling expenses amount,Electricity charges amount and Other Expenses amount are assigned.

If i decreases the Expenses in slider, then automatically my Current Revenue have to increases and

should moves to the target.How can i do this.Please give me the code to do it.Please help me.