Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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...
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
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
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;