Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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
robin_hausdoerfer
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

robin_hausdoerfer
Valued Contributor III

Re: splitting yearly budget to months in script?

I meant a qvs file...