Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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
Highlighted
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

Highlighted
Not applicable

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

Highlighted
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

Highlighted
Not applicable

Thanks Antonio

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