topic Re: All Months between two dates & divide Condition in QlikView

All Months between two dates & divide Condition

sspawar88 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: All Months between two dates & divide Condition

Anil_Babu_Samineni — Mon, 21 Nov 2016 09:16:16 GMT

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

Re: All Months between two dates & divide Condition

veeranj — Mon, 21 Nov 2016 11:50:05 GMT

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

Re: All Months between two dates & divide Condition

anushree1 — Wed, 23 Nov 2016 11:58:33 GMT

Hi,

Please check the solution in the attached file