splitting yearly budget to months in script?

[Anonymous]

i have 2 tables:

one containing actual spending and earnings per category per month e.g:

month	category	Amount
Jan 2016	Raw materials	-20000
Jan 2016	Salaries	-35000
Jan 2016	Income	40000
Feb 2016	Raw materials	-22000
Feb 2016	Salaries	-36000
Feb 2016	Income	50000

and another containing a yearly budget per category e.g.:

Year	Category	Yearly Budget
2016	Raw materials	120000
2016	Salaries	500000
2016	Income	1000000

I need to display the actual amounts vs. monthly budget by dividing the yearly budget by 12 in a pivot table and in a bar chart with the actual amounts as bars and the budget as a marker for each month.

If I add a measure to the pivot table dividing the budget amount  by 12 : Sum(YearlyBudget)/12    the total figures in the pivot table don't add up the 12 monthly amount into one yearly amount

How to i turn the budget yearly data into  a 12 monthly rows per category and add a month name to each row directly in the script? of if there is another alternative for displaying the data correctly i will be happy to hear about it .

[Anonymous]

please upload a sample file...

[Anonymous]

Attached is a sample file.

I used a crosstable in the script on the actual tables to have all dates in one column.

[antoniotiman]

Hi Anat,

in the script

LOAD Date#(month,'MMM YYYY') as Month,Year(Date#(month,'MMM YYYY')) as Year,
category as Category,
Amount
FROM
"https://community.qlik.com/message/1272206"
(html, codepage is 1252, embedded labels, table is @1);
Left Join
LOAD Year,
Category,
[Yearly Budget]/12 as Budget
FROM
"https://community.qlik.com/message/1272206"
(html, codepage is 1252, embedded labels, table is @2);

Regards,

Antonio

[Anonymous]

I meant a qvs file...