Hi,
I am brand new to Jet and just starting to try and learn it. Normally I would take time to read up everywhere and try to find the answer myself from previous threads etc., but I am under a lot of time pressure just now so thought someone might be able to offer some help and guidance.
I am trying to create a simple report which compares sales in 2 periods and gives the difference between them. Basically we are dealing with just 2 fields from the Customer table: No. and Sales (LCY). I have Sales (LCY) in two columns, each with a different Date FIlter applied (Code: =NF($D8,"Sales (LCY)","Date Filter",$G$6). I can then use a simple Excel formula to calculate the difference between the two periods. This works fine.
Now the questions:
1) How do I sort by one of the Sales (LCY) columns so the largest value is at the top. I tried the following NL function, but I have something wrong in it:
=NL("Rows","Customer",,"-NF(,""Sales (LCY)"",""date filter"",""010112..311212"")","*")
I am not sure of the exact syntax of nested functions - in particular when you need two sets of double quotes "". If someone could explain briefly that would be great.
2) The next step would be to sort by the Difference column. I think I am correct in saying that the sorting has to be included in the NL function, so presumably I would need to build a calculation into the NL function to compare the sales between the two periods (perhaps using two NF functions to get the two figures) before I could then sort by the resulting value. I don't think I can sort the column if it just contains a simple excel formula, but I don't really know how to begin on this just now.
Any help or suggestions to get me over this hurdle would be appreciated, and sorry for asking first and researching later.
Thanks,
Garry.
Date
Votes
2 comments
-
Jet Reports Historic Posts Official comment Hi Garry,
1) You are trying to make something simple too complicated here. You only need a calculated filter field like you are trying to do in cases where you need to do something complicated like sort by a sum. In your case, you should just be able to do something much simpler like this:=NL("Rows","Customer",,"-Sales (LCY)","*","date filter","010112..311212")
2) Now this is where you would actually need a calculated filter field since what you are trying to sort by is only achievable by using an Excel formula that must be calculated for each row of the Customer table. Note that the reason your original attempt didn't work was mainly because you left out the = sign in front of your formula; otherwise it was mostly correct. It might look like this:=NL("Rows","Customer",,"-=NF(,""Sales (LCY)"",""date filter"",""010112..311212"")-NF(,""Sales (LCY)"",""Date Filter"",""[Your other date range]"")","*")
The significance of the "" is that the filter is already an Excel string surrounded by quotes. In order to insert a quote character into a string in Excel, you have to "escape" it by adding 2 quote characters. Otherwise, Excel will just think the quote character is there to end the string, which would make the whole formula invalid. Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
Wonderful. Does just what I need.
Thanks so much for your help.
Regards,
Garry.
Please sign in to leave a comment.