topic Help with Peek/Previous in QlikView

Help with Peek/Previous

Wed, 25 Nov 2020 16:16:04 GMT

I am trying to write a Quote analysis report, the problem being that the sales people are using Revisions rather than change the original quotation. I have been asked to write the report to show the difference in the Value.

Below is an example of the data, What I need to create is the Highlighted field.

The logic is - if the Suffix is -1 then that is the adjusted value.

If any other suffix it is the last value is the new value minus that last revision.

Hopefully the data below is clear. I have looked at using Peek & Previous but I don't seem to get any meaningful data.

Any help or pointer would gladly be received.

Cheers

Dave

Estimate_Ref	Estimate ID	SOPH_Status	SOPH_Document_Type	SOPH_Header_Type	CalendarMonthYear	Sum(Goods_Value)	Adjusted Value
EST010010-1	EST010010	R	SER	E	01/12/2015	1,140.00	1,140.00
EST010010-2	EST010010	O	SER	E	01/01/2016	1,140.00	£0.00
EST010011-1	EST010011	N	SP	E	01/12/2015	57.67	57.67
EST010012-1	EST010012	N	SP	E	01/12/2015	743.00	743.00
EST010013-1	EST010013	O	SER	E	01/12/2015	904.73	904.73
EST010014-1	EST010014	N	SP	E	01/12/2015	1,462.30	1,462.30
EST010015-1	EST010015	O	SER	E	01/12/2015	670.00	670.00
EST010016-1	EST010016	O	SER	E	01/12/2015	5,730.08	5,730.08
EST010017-1	EST010017	X	SP	E	01/12/2015	63.18	63.18
EST010018-1	EST010018	R	SP	E	01/12/2015	1,641.29	1,641.29
EST010018-2	EST010018	O	SP	E	01/01/2016	10,510.89	8,869.60
EST010019-1	EST010019	X	SER	E	01/12/2015	27,615.00	27,615.00
EST010019-2	EST010019	O	SER	E	01/12/2015	26,100.00	-1,515.00
EST010020-1	EST010020	X	SER	E	01/12/2015	5,630.00	5,630.00
EST010021-1	EST010021	O	SP	E	01/12/2015	2,444.34	2,444.34
EST010021-2	EST010021	O	SP	E	01/01/2016	3,021.27	576.93
EST010054-1	EST010054	R	SP	E	01/12/2015	569.78	569.78
EST010054-2	EST010054	R	SP	E	01/12/2015	569.78	0.00
EST010054-3	EST010054	O	SP	E	01/01/2016	217.28	-352.50

Re: Help with Peek/Previous

antoniotiman — Thu, 24 Aug 2017 16:18:25 GMT

Hi Dave,

LOAD

............,

If(SubField(Estimate_Ref,'-',-1)=1,[Sum(Goods_Value)],[Sum(Goods_Value)]-Previous([Sum(Goods_Value)])) as [Adjusted Value]

From Table;

Regards,

Antonio

Re: Help with Peek/Previous

Fri, 25 Aug 2017 08:00:42 GMT

Hi Antonio

Thanks for your quick reply. I have tried adding the script which seems to do something but the values appear to be picking up different previous values, does the data have to be ordered in the script to pick up the correct previous value??

Regards

Dave

Re: Help with Peek/Previous

antoniotiman — Fri, 25 Aug 2017 08:12:03 GMT

Dave,

I've loaded as Inline File ordered as I can see.

If You need specific order in Your Data You add

Order By Field1,Field2,..

to LOAD statement.

Re: Help with Peek/Previous

Fri, 25 Aug 2017 08:14:04 GMT

Thanks Antonio

I have ordered the data and it is all working as I needed. Thankyou for your help