Set Analisys for residual calculation for date

[#1]

Greetings to all!

In the document there is a table of residuals of the goods. This table as follows looks:

GoodId

ShopId

RDate

RestQuant

RDate - date on which is actual record about residual. Format MM.DD.YYYY

RestQuant - goods residual.

The problem in that that in the table is not all dates. For example on the goods X in shop Y it looks so:

GoodId	ShopId	RDate	RestQuant
X	Y	01.01.2011	10
X	Y	01.02.2011	9
X	Y	01.03.2011	8
X	Y	01.05.2011	6
X	Y	01.07.2011	10
X	Y	01.09.2011	15

How I can calculate goods residual for date of  01.04.2011 (for example) ?

I ask a pardon for my knowledge of English.

[kaushiknsolanki]

Hi,

   If your database from where you pulled the data, is not having any entry for 01.04.2011 date then how will you see that in the table.

   So make sure that you have any transaction on that day.

Regards,

Kaushik Solanki

[#3]

Residual for date of 1.04.2011 will be equal to residual for date of 1.03.2011 and will be equal 8.

Excuse, has forgotten to add that in the table the data only for those days contains when residual changed. That is if the data for specific day isn't present, residual (RestQuant) is equal to residual for the previous date.

I can receive the previous date here so:

MAX({1 <[RDate] = {"=[RDate] <= num(vrStDate)"}> }[Rdate])

Where vrStDate a variable.

But hasn't guessed how to receive residual yet...

[nagaiank]

You may add data for the missing dates based on the previous day's data. A sample script for doing this will be something like this:

Temp:

LOAD GoodId, ShopId, Date(Date#(RDate,'MM.DD.YYYY')) as RDate, RestQuant;

LOAD * Inline [

GoodId,ShopId,RDate,RestQuant

X,Y,01.01.2011,10

X,Y,01.02.2011,9

X,Y,01.03.2011,8

X,Y,01.05.2011,6

X,Y,01.07.2011,10

X,Y,01.09.2011,15

];

Temp1:

LOAD RDate as xx Resident Temp Order by RDate asc;

LET vDateMin = Num(Peek('xx',  0, 'Temp1'));

LET vDateMax = Num(Peek('xx', -1, 'Temp1'));

DROP Table Temp1;

Temp2:

LOAD Date($(vDateMin)+IterNo()-1) as RDate AutoGenerate 1 While $(vDateMin)+IterNo()-1 <= $(vDateMax);

Left Join (Temp2) LOAD * Resident Temp;

DROP Table Temp;

Data:

NoConcatenate

LOAD RDate,

     If(Len(GoodId)>0,GoodId,Peek(GoodId)) as GoodId,

     If(Len(ShopId)>0,ShopId,Peek(ShopId)) as ShopId,

     If(Len(RestQuant)>0,RestQuant,Peek(RestQuant)) as RestQuant

    Resident Temp2 Order By RDate;

DROP Table Temp2;

Hope this helps.

[jedgson]

As per Kaushik, if there is no date in your table for 01.04.2011 then that date will not show.

Firstly you will need to create a 'Calendar' table that contains all dates to ensure you are not missing any:

DateTable:

Load

     num('01/01/2011') + recno() -1 as DT_Date

AUTOGENERATE (YearEnd(today()) - num('01/01/2011'));

The above will generate a table that contains every date from 01/01/2011 to 31/12/2011.

You can then use something allong the following lines in your chart exression

If( isnull(Sum(RestQuant)), above(sum(RestQuant),1), Sum(RestQuant))

Jay