Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

agdgn123
New Contributor III

splitting yearly budget to months in script?

i have 2 tables:

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

monthcategoryAmount
Jan 2016Raw materials-20000
Jan 2016Salaries-35000
Jan 2016Income40000
Feb 2016Raw materials-22000
Feb 2016Salaries-36000
Feb 2016Income50000

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

YearCategoryYearly Budget
2016Raw materials120000
2016Salaries500000
2016Income 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 .

4 Replies
roharoha
Valued Contributor III

Re: splitting yearly budget to months in script?

please upload a sample file...

agdgn123
New Contributor III

Re: splitting yearly budget to months in script?

Attached is a sample file.

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

antoniotiman
Honored Contributor III

Re: splitting yearly budget to months in script?

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

roharoha
Valued Contributor III

Re: splitting yearly budget to months in script?

I meant a qvs file...

Community Browser