Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 .
please upload a sample file...
Attached is a sample file.
I used a crosstable in the script on the actual tables to have all dates in one column.
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
I meant a qvs file...