Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Here i have following Data Table
Date | Period | Actual Revenue |
01/01/2016 | 12 | 15000 |
01/06/2016 | 12 | 20000 |
01/10/2016 | 4 | 5000 |
On 01/01/2016 the Revenue 15000 was decided for Next 12 Months (Period) but due to some reason on 01/06/2016 it has been changed to 20000 for Next 12 Months then again it get changed for 4 Period on 01/10/2016.
Actual revenue is divide by Period.
How Can i get following output in Qlikview
Thanks In advance
Try Something like this in script
Load * inline [
Facts_Dates,Period, Actual_Revenue
01-Jan-2016,12, 1000
01-Jun-2016,12, 2000
01-Sep-2016,4, 3000
];
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min([Facts_Dates]) as minDate,
max([Facts_Dates]) as maxDate
Resident Facts;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS [Facts_Dates],
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
And Then, Create straight Table with the following
Dimensions are ---- Facts_Dates
Expression is --- Sum(Actual_Revenue) / Sum(Period)
Hope this helps
Hi,
Try this .
tab:
LOAD * INLINE [
Date, Period, Actual Revenue
01/01/2016, 12, 15000
01/06/2016, 12, 20000
01/10/2016, 4, 5000
];
tab1:
LOAD *,if(isnull(Previous(Date)),today(),Previous(Date)) as nextdate Resident tab order by Date desc ;
drop table tab;
tab2:
load addmonths(Date,iterno()-1) as Newdate,* Resident tab1 while addmonths(Date,IterNo()-1) <nextdate;
drop table tab1;
Thanks,
Veera
Hi,
Please check the solution in the attached file