0 Replies Latest reply on Oct 25, 2018 7:07 AM by sabari.shrestha.0

    How to calculate Discrete Count of Number of Records before the Highest one

    sabari.shrestha.0

      Hi, I am counting the number of times (rows) a user made transactions before they made their highest yet.

       

      These are the criteria:

      1. If a person's transaction is same for all the dates, I am returning the first date and number of counts before the highest transaction will be 0.

       

      2. If the person's highest transaction is the first one, I am returning the first date and number of counts as 0.

       

      3. If the person's highest transaction is after few numbers of transactions , I am counting the number of transaction before that highest one. For example, here for userID 104, the highest is $200 and 5th column is showing on which date he made, 7th columns shows the max transaction. Here I am counting the number of rows before $200, which will be 5. (which I have  showed in a worksheet 2)

       

                                

      I believe I am good upto this step. Right now, I am getting the counts for individual users. But I want to see how many users performed the transaction before they made their highest transaction. I want the "Counts before highest" as discrete so that I can count the number of users in each counts . For my sheet 2, I want to return something like this. 2nd Column tells 16 users made highest transaction on their 1st one, 4 users made 1 transaction before they made their highest and so no...

      Right now, if I remove the user ID and date columns, it gives me only the sum total .