All Months between two dates & divide Condition

[sspawar88]

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

[Anil_Babu_Samineni]

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

[veeranj]

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

[anushree1]

Hi,

Please check the solution in the attached file