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

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.

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

Case(

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

Thanks

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

Thanks