topic [ASK] Latest transaction quantity in QlikView

[ASK] Latest transaction quantity

rendiyan — Fri, 29 Jan 2016 06:56:36 GMT

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

Re: [ASK] Latest transaction quantity

Anonymous — Fri, 29 Jan 2016 12:25:39 GMT

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

Re: [ASK] Latest transaction quantity

sunny_talwar — Fri, 29 Jan 2016 13:07:30 GMT

Try to do this in Script or front end?

Re: [ASK] Latest transaction quantity

PradeepReddy — Fri, 29 Jan 2016 13:19:31 GMT

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;

Re: [ASK] Latest transaction quantity

sunny_talwar — Fri, 29 Jan 2016 13:24:11 GMT

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;

Re: [ASK] Latest transaction quantity

PradeepReddy — Fri, 29 Jan 2016 15:42:38 GMT

Your code works perfectly, as per the requirement..