Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help with Peek/Previous

Hi

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_RefEstimate IDSOPH_StatusSOPH_Document_TypeSOPH_Header_TypeCalendarMonthYearSum(Goods_Value)Adjusted Value
EST010010-1EST010010RSERE01/12/20151,140.001,140.00
EST010010-2EST010010OSERE01/01/20161,140.00£0.00
EST010011-1EST010011NSPE01/12/201557.6757.67
EST010012-1EST010012NSPE01/12/2015743.00743.00
EST010013-1EST010013OSERE01/12/2015904.73904.73
EST010014-1EST010014NSPE01/12/20151,462.301,462.30
EST010015-1EST010015OSERE01/12/2015670.00670.00
EST010016-1EST010016OSERE01/12/20155,730.085,730.08
EST010017-1EST010017XSPE01/12/201563.1863.18
EST010018-1EST010018RSPE01/12/20151,641.291,641.29
EST010018-2EST010018OSPE01/01/201610,510.898,869.60
EST010019-1EST010019XSERE01/12/201527,615.0027,615.00
EST010019-2EST010019OSERE01/12/201526,100.00-1,515.00
EST010020-1EST010020XSERE01/12/20155,630.005,630.00
EST010021-1EST010021OSPE01/12/20152,444.342,444.34
EST010021-2EST010021OSPE01/01/20163,021.27576.93
EST010054-1EST010054RSPE01/12/2015569.78569.78
EST010054-2EST010054RSPE01/12/2015569.780.00
EST010054-3EST010054OSPE01/01/2016217.28-352.50
1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

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.

View solution in original post

4 Replies
antoniotiman
Master III
Master III

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

Not applicable
Author

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

antoniotiman
Master III
Master III

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.

Not applicable
Author

Thanks Antonio

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