[ASK] Latest transaction quantity

[rendiyan]

Dear QlikView Developer,

I have a report that have to show current transaction and latest transaction before current date, as below sample i made.

Date	Qty	DateY	QtyY
4/4/2010	25	4/3/2010	0
4/5/2010	32	4/4/2010	25
4/6/2010		4/5/2010	32
4/7/2010		4/6/2010	32
4/8/2010	25	4/7/2010	32
4/9/2010		4/8/2010	25
4/10/2010	23	4/9/2010	25
4/11/2010		4/10/2010	23
4/12/2010	29	4/11/2010	23

Date, and Qty column are coluns from transaction table.

And the users request to add yesterday quantity, if yesterday quantity is null then get the latest transaction quantity.

Sample above i created manually from excel to show what my users requests.

How to solve this?

Thanks,

Best Regards

[Anonymous]

For latest Transaction try like this?

=sum({<Date={'=$(=Date(max(Date),'DateFormat'))'}>} Qty)

For Previous Latest, like this?

=sum({<Date={'=$(=Date(max(Date)-1,'DateFormat'))'}>} Qty)

Note: Format of Date & DateFormat should be same

For You Null handling, may be like this?

=if(isnull(sum({<Date={'=$(=Date(max(Date),'DateFormat'))'}>} Qty)),

sum({<Date={'=$(=Date(max(Date)-1,'DateFormat'))'}>} Qty),    //Condition True

sum({<Date={'=$(=Date(max(Date),'DateFormat'))'}>} Qty))   //For False

[sunny_talwar]

Try to do this in Script or front end?

[PradeepReddy]

try like this..

Temp:

Load * inline

[

Date,Qty,Date

4/4/2010,25,4/3/2010

4/5/2010,32,4/4/2010

4/6/2010,,4/5/2010

4/7/2010,,4/6/2010

4/8/2010,25,4/7/2010

4/9/2010,,4/8/2010

4/10/2010,23,4/9/2010

4/11/2010,,4/10/2010

4/12/2010,29,4/11/2010

];

Test:

Load *,

if(len(trim(Qty))=0,peek(QtyY),Qty) as QtyY

resident Temp;

drop table Temp;

[sunny_talwar]

Slight alteration to the code:

Temp:

LOAD * inline [

Date,Qty,DateY

4/4/2010,25,4/3/2010

4/5/2010,32,4/4/2010

4/6/2010,,4/5/2010

4/7/2010,,4/6/2010

4/8/2010,25,4/7/2010

4/9/2010,,4/8/2010

4/10/2010,23,4/9/2010

4/11/2010,,4/10/2010

4/12/2010,29,4/11/2010

];

Test:

LOAD *,

  Alt(If(Len(Trim(Peek('Qty')))=0, Peek('QtyY'), Peek('Qty')), 0) as QtyY

resident Temp

Order By Date;

DROP Table Temp;

[PradeepReddy]

Your code works perfectly, as per the requirement..