Split cost by year

[#1]

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

[MK_QSL]

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:

Load * Inline

[

  Date

  01/04/2014

  04/04/2015

];

Budget:

Load * Inline

[

  Budget

  16000

];

Temp:

Load

  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:

Load *, AutoNumber(YearMonth,'YearMonth') as YearMonthID;

Load

  Date(TempDate) as Date,

  Month(TempDate) as Month,

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

  Year(TempDate) as Year;

Load

  $(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:

Load

  Date,

  Month,

  YearMonth,

  Year,

  YearMonthID,

  $(vMonthlyAmount) as MonthlyBudget

Resident Calendar;

Drop Table Calendar;

Drop Field YearMonthID;

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

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

[#3]

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

[#4]

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

[MK_QSL]

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:

Load * Inline

[

  Date

  01/04/2014

  04/04/2015

];

Budget:

Load * Inline

[

  Budget

  16000

];

Temp:

Load

  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:

Load *, AutoNumber(Date,'YearMonth') as DateID;

Load

  Date(TempDate) as Date,

  Month(TempDate) as Month,

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

  Year(TempDate) as Year;

Load

  $(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:

Load

  Date,

  Month,

  YearMonth,

  Year,

  DateID,

  '$(vDayCount)' as DailyBudget

Resident Calendar;

Drop Table Calendar;