7 Replies Latest reply on Oct 4, 2015 6:32 PM by user19752

    Portal Sorting


      Hi Everyone

      This is the second time I write this question but I am not sure what happen to the first one.


      I have a simple solution to record sales, some what like the invoicing solution. I have 3 Tables




      and they are related via primary keys. In the sales portal that is on School layout (Sorted by Date, descending) I have 6 fields


      Gross Sales


      Avg Sales


      Created on

      I need to have the Filed CHANGES show me the difference between last year's Gross and this year's Gross in Percentage

      The calculation I have is


      Get ( RecordNumber )  >  1 and not IsEmpty ( GrossSales) ; Round (GrossSales/ GetNthRecord ( GrossSales ; Get ( RecordNumber )  -  1 )   - 1;  4 )


      The problem is that the latest CHANGES is blank and other rows are calculated correctly but I need the first record to show the difference between the latest record and any other after that.

      I hope that make sense.


        • 1. Re: Portal Sorting

          I guess you want +11% on 1st record(2015),


          Let ( prev = GetNthRecord ( GrossSales ; Get ( RecordNumber ) + 1 ) ;

          Case( not IsEmpty ( prev ) ; Round (GrossSales / prev ; 4 ) )



          You are better to post Discussions

          • 2. Re: Portal Sorting

            Thank you for you response.


            I had to add - 1 after Prev to get the result I was looking for. Your formula was great however it didn't give me the difference. I also am getting a ? in the first row. How can I get rid of the ? in the first record?


            Let ( Prev= GetNthRecord ( GrossSales; Get ( RecordNumber ) + 1) ;



            not IsEmpty ( Prev ) ; Round ( GrossSales / Prev  - 1 ; 4 ))



            • 3. Re: Portal Sorting

              Sorry I can't get what you want.


              I guessed that you want +11.11% at first row (2015) comparing 1000 to 900, don't you?


              But you wrote -10.00% in second row (2014) is correct. This is comparing 900 to 1000 (this year 2014 to next year 2015). If this is correct, you don't have value (in row 2016) to compare on first row, so the result should be empty, there is no problem in the screen shot...

              • 4. Re: Portal Sorting

                sorry you are right, I needed the +11.11 to be in the first row in 2015 record and by adding a -1 to your formula everything works fine now but as I mentioned there is a ? In the oldest record i.e. 2013

                How can I get rid of the ? in the first record

                I used conditional formatting to hide the ? But  I know there's a better way through the formula you sent me


                • 5. Re: Portal Sorting

                  -1 is not need there.

                  -1 in your calculation first post mean "row position -1", but indeed you need to compare "row position +1", so in my calculation it is change to +1.

                  prev variable has value 900 at first fow, so -1 has no good effect.


                  Try changing IsEmpty(prev) to IsValid(prev)

                  • 6. Re: Portal Sorting

                    Sorry to confuse you, my English is not that great so please forgive me

                    Here is what I have right now


                    Let ( prev = GetNthRecord ( GrossSales ; Get ( RecordNumber ) + 1 ) ;

                    Case( IsValid( prev ) ; Round (GrossSales / prev -1 ; 4 ) )



                    The -1 that I was talking about was in

                    Round (GrossSales / prev -1 ; 4 )


                    If I don't add that the formula shows 2015 as 111.11% and the 2104 as 112.50%

                    By adding the -1 after prev I get the 11.11% and 12.5%


                    After changing the "not IsEmpty" to "not isValid" Everything disappears but when I change the not isEmpty to isValid the calculations return however the question mark stays in the first record.


                    Hope these explanations make sense.



                    • 7. Re: Portal Sorting

                      Ouch, I misread your post. I also not good at English


                      So changing

                      Case ( prev + 0 <> 0 ; ....

                      will do it, the cause getting ? is dividing by 0 (or null/empty).