Budgeting

[tomovangel]

Hello guys, I have got the following problem.

I have 2 tables
1 for budgets

1 for sales .

I am using a Combo chart with Months as dimensions and Sales as expression.

I have included second expression which is Sum(budget).

But whenever I select for example Q3, it shows me all the items, which I have sold for the period and I want it to show the budget even for those items, that I havent sold.

For example if my budget for august is 10 dollars for 10 items, that gives a total of 100

But if I have actually sold only 9 items, my budget is 90 dollars. ( how can I show 100 dollars budget here)

I was thinking of using some kind of Set analysis, but couldn't do it.

Any suggestions?

[prat1507]

Hi Could you provide the structure for the two tables?

Regards
Pratyush

[agigliotti]

or even better the qvf file with sample data.

[Anonymous]

just create a link table with all common dimensions of budget and sales...

and connect a calendar with the link table date field.

[tomovangel]

I am working through RDP, on my client's server in Austria, and I can't send anything from it.

I am using my personal computer for communications here. Sorry..

My sales data is from a datawarehouse and has around 150 columns
but from them I use around 40, my table is named Sales

My budget data is from excel file and i have I

1. Item Number

2.Description

3.Sales QTY

4.Product Line

5.Year

6.Month

7.Price CHF

8. Budget value

All the Fieldnames from the excel file, are present in the Sales table,
so i was wondering if Concat is possible, but Im not sure if it's not going to disrupt my other Data..

[tomovangel]

Yes, but I want to show all the budgeted items,
Not only the budgeted Items, that I have sold , but all
i have used a set expression, but it' doesn't work as intended...

Sum({$<YEAR={2017}>+1<[Country]-{"Bulgaria"}>}Budget)

[Anonymous · Accepted solution]

off course, that's why the link table.

example:

LINKTABLE:

LOAD distinct COMMONFIELD1, COMMONFIELD2, ... COMMONFIELD_N from BUDGETTABLE;

concatenate

LOAD distinct COMMONFIELD1, COMMONFIELD2, ... COMMONFIELD_N from SALESTABLE;

If you don't won't a "synthetic" Link Table, then you could use autonumber()...

[tomovangel]

I have done what I was looking for with the following expression

IN my bar chart i have Month as dimension and Sales as expression 1

expression 2 is Sum({1}Budget)