topic Re: DIVIDE VALUES PROPORTIONALLY TO THE AFFECTED MONTHS in QlikView

DIVIDE VALUES PROPORTIONALLY TO THE AFFECTED MONTHS

inigoelv — Sun, 13 Jan 2013 12:38:29 GMT

The object of this discussion is whether there is any function that divides the values proportionally to the affected months.

Per example, if I have two waste

Waste	Quantity Ton	Initial date of storage	Final date of storage
A	10	01/01/2012	15/03/2012
B	20	24/02/20012	03/04/2012

What I want is to know the quantity generated per month or what is the same:

Waste	January	February	March	April
A	4,18	3,78	2,04
B		2,10	16,3	1,6

Thanks in advance,

Re: DIVIDE VALUES PROPORTIONALLY TO THE AFFECTED MONTHS

nagaiank — Sun, 13 Jan 2013 14:11:29 GMT

The attached qvw file one way of implementing what you need.

Re: DIVIDE VALUES PROPORTIONALLY TO THE AFFECTED MONTHS

inigoelv — Sun, 13 Jan 2013 14:32:06 GMT

Thanks krishnamoorthy, but i have a personal edition of qlikview and I can not open it.

Could you explain the formulas directly?

Thanks in advance,

Re: DIVIDE VALUES PROPORTIONALLY TO THE AFFECTED MONTHS

nagaiank — Sun, 13 Jan 2013 14:37:53 GMT

The script used is given below:

Then create a pivot table chart with Waste and Month as dimensions and Sum(DailyWaste) as expresion to get the result table you want to generate.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='D/M/YYYY';

SET TimestampFormat='D/M/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Data:

LOAD *

, [Quantity Ton] / DayCount as DailyQty;

LOAD *

, [Final date of storage] - [Initial date of storage] + 1 as DayCount;

LOAD * Inline [

Waste,Quantity Ton,Initial date of storage,Final date of storage

A,10,01/01/2012,15/03/2012

B,20,24/02/2012,03/04/2012

];

Temp2:

LOAD Min([Initial date of storage]) as MinDate

, Max([Final date of storage]) as MaxDate

Resident Data;

Let vDateMin = Peek('MinDate',-1,Temp2);

Let vDateMax = Peek('MaxDate',-1,Temp2);

Drop Table Temp2;

Calendar:

LOAD $(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS Date

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1 <= $(vDateMax);

Outer Join (Data) LOAD Date Resident Calendar;

DROP Table Calendar;

Result:

LOAD *, MonthName(Date) as Month;

LOAD *

,If (Date >= [Initial date of storage] and Date <= [Final date of storage], DailyQty, 0) as DailyWaste;

LOAD * Resident Data;

DROP Table Data;

Re: DIVIDE VALUES PROPORTIONALLY TO THE AFFECTED MONTHS

inigoelv — Sun, 05 May 2013 13:50:12 GMT

Hello:

At the end the solution was:

1) In the script to create a file by moth affected ans waste:

LOAD

[Waste],

[Quantity Ton],

[Initial date of storage],

[Final date of storage],

Date(AddMonths(MonthStart ([Initial date of storage]),iterno()-1),'MMMYYYY') as Month

FROM

(ooxml, embedded labels, header is 3 lines, table is [Waste])while monthstart([Final date of storage]) >= addmonths(monthstart([Initial date of storage]),iterno

()-1);

2) In the espression:

=Sum ([Quantity Ton]/([Final date of storage]-[Initial date of storage]))*(If ([Initial date of storage]>=(MonthStart(Month)), MonthEnd (Month)-[Initial date of storage],
If([Final date of storage]<=(MonthEnd(Month)),[Final date of storage]-MonthStart(Month),
MonthEnd (Month)-MonthStart(Month))))

I hope it will be helpfull.