Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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_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 |
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.
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
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
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.
Thanks Antonio
I have ordered the data and it is all working as I needed. Thankyou for your help