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

    Portal Sorting

    mehbastan

      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

      School

      Sales

      Sales_Items

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

      Date

      Gross Sales

      Rebates

      Avg Sales

      Changes

      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

      Case(

      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.

      Capture.JPG

        • 1. Re: Portal Sorting
          user19752

          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) ;

             

            Case(

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

             

            )

            • 3. Re: Portal Sorting
              user19752

              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

                Thanks

                • 5. Re: Portal Sorting
                  user19752

                  -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.

                     

                    Thanks

                    • 7. Re: Portal Sorting
                      user19752

                      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).