topic Re: Split cost by year in Qlik Learning Discussions

Split cost by year

Fri, 13 Feb 2015 20:30:34 GMT

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

MK_QSL — Sat, 14 Feb 2015 17:01:42 GMT

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...

Re: Split cost by year

Fri, 20 Feb 2015 08:26:17 GMT

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

Fri, 20 Feb 2015 09:32:15 GMT

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

MK_QSL — Fri, 20 Feb 2015 14:45:44 GMT

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;