5 Replies Latest reply: Feb 20, 2015 9:45 AM by Manish Kachhia

# Split cost by year

Hi ,

i have some project related data , i intend to show the budget in each year ,

if the start date(MM-DD-YYYY) is : 01/04/2014

and the end date is: 04/04/2015

budget is 16000

then the duration is 16 months

average budget per month :1000

budget for year 2014:12000/-

budget for year 2015:4000/-

how can this been done through Qlikview expressions , i need to show a pie for total budget and then when a year is selected , the budget in that year

now i intend to show the budget  year wise

• ###### Re: Split cost by year

use below script

=======================

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Date:

[

Date

01/04/2014

04/04/2015

];

Budget:

[

Budget

16000

];

Temp:

Min(Date) as MinDate,

Max(Date) as MaxDate

Resident Date;

Let vMinDate = NUM(PEEK('MinDate',0,'Temp'));

Let vMaxDate = NUM(PEEK('MaxDate',0,'Temp'));

Calendar:

Date(TempDate) as Date,

Month(TempDate) as Month,

Date(MonthStart(TempDate),'YYYYMM') as YearMonth,

Year(TempDate) as Year;

\$(vMinDate) + IterNo() - 1 as TempDate

AutoGenerate 1

While \$(vMinDate) + IterNo() - 1 <= \$(vMaxDate);

MaxYearMonthID:

Load Max(YearMonthID) as MaxMonthCount Resident Calendar;

Drop Table Temp;

Let vBudget = Num(Peek('Budget',0,'Budget'));

Let vYearMonthCount = Num(Peek('MaxMonthCount',0,'MaxYearMonthID'));

Let vMonthlyAmount = \$(vBudget)/\$(vYearMonthCount);

FinalTable:

Date,

Month,

YearMonth,

Year,

YearMonthID,

\$(vMonthlyAmount) as MonthlyBudget

Resident Calendar;

Drop Table Calendar;

Drop Field YearMonthID;

===========================

Now you can use SUM(MontlyBudget) as expression...

• ###### Re: Split cost by year

Hi Manish,

when i am plotting  a pie chart with year as dimension and doing sum(MonthlyBudget) the value is not showing correctly

as in 12000 for 2014

and 4000 for 2015

on viewing the table viewer i realise the place we are going wrong

when the budget is being split by day for every day we  have the amount 1000 corresponding to it while it should be 1000 divided by no of days in that month

would be really helpful if you can guide me through

thanks a lot

really appreciate the help

• ###### Re: Split cost by year

hey manish

i was ablle to do that , however i do not wish to divide the cost by number of days is it possible i simply assign the first day in a month value 1000 and the remaining zero

• ###### Re: Split cost by year

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Date:

[

Date

01/04/2014

04/04/2015

];

Budget:

[

Budget

16000

];

Temp:

Min(Date) as MinDate,

Max(Date) as MaxDate

Resident Date;

Let vMinDate = NUM(PEEK('MinDate',0,'Temp'));

Let vMaxDate = NUM(PEEK('MaxDate',0,'Temp'));

Calendar:

Date(TempDate) as Date,

Month(TempDate) as Month,

Date(MonthStart(TempDate),'YYYYMM') as YearMonth,

Year(TempDate) as Year;

\$(vMinDate) + IterNo() - 1 as TempDate

AutoGenerate 1

While \$(vMinDate) + IterNo() - 1 <= \$(vMaxDate);

MaxDateCount:

Load Max(DateID) as MaxDateCount Resident Calendar;

Drop Table Temp;

Let vBudget = Num(Peek('Budget',0,'Budget'));

Let vDayCount = Num(Peek('MaxDateCount',0,'MaxDateCount'));

Let vDailyAmount = \$(vBudget)/\$(vDayCount);

FinalTable:

Date,

Month,

YearMonth,

Year,

DateID,

'\$(vDayCount)' as DailyBudget

Resident Calendar;

Drop Table Calendar;