Chart Sls vs Bud X-Axis Months

[#1]

Hi Guys

I have two QVD tables:

RSRR (Date / RepCode / BudgetValue)

Sales (Date / RepCode / SalesValue)

How do I Chart Sales by Month for Budget and Sales by Month on same Chart.  What dimension do I use to get the X-Axis with J, F, M, A etc

[hic · Accepted solution]

First, you should concatenate the two tables:

Facts:

Load Date, RepCode, BudgetValue as Amount, 'Budget' as Type From Budget.qvd;

Load Date, RepCode, SalesValue as Amount, 'Sales' as Type  From Sales.qvd;

Then you should create a Master calendar:

Calendar:

Load distinct Date, Month(Date) as Month, Year(Date) as Year Resident Facts;

Then you can create your chart:

Dimensions: Year, Month, RepCode and/or Type

Expression: Sum(Amount)

HIC

[#3]

THanks HC

Any chance you could show me how to use the Combo Chart where Type=Sales is a Line and Type=Budget is a Bar?

[hic]

You need to have Sales and Budget in two different expressions. One way is to define them as

     Sum({$<Type={'Sales'}>} Amount) and

     Sum({$<Type={'Budget'}>} Amount)

Or you can define two different fields in the script already:

Facts:

Load Date, RepCode, BudgetValue From Budget.qvd;

Concatenate

Load Date, RepCode, SalesValue From Sales.qvd;

and use

     Sum(SalesValue) and

     Sum(BudgetValue)

When you have them as two different expressions, you can set the display options (Properties - Expressions) as 'Bar' for one and 'Line' for the other.

HIC

[#5]

Hi HC

Wanting another Combo Chart, but on BudgetValue versus QuotedValue:

Fact:

Load Date, CustCode, BudgetValue as Amount, 'Budget' as Type From Budget.qvd;

Load Date, [CustCode], QuotedValue as Amount, 'Sales' as Type  From Quoted.qvd;

Things is, the [CustCode] does not exist in the Quoted.qvd, but in a another qvd called Jobs.qvd

The join between Quoted.qvd and Jobs.qvd could be QuoteNum (if this help you). How do I get the CustCode into this?

Any advice on this?  Thanks

[hic]

I would use Applymap() with QuoteNum as key. See http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap

HIC