3 Replies Latest reply on Jan 2, 2018 9:55 PM by Lihos G

    LOD Expression and Binning of Categorical Value

    Lihos G

        I am working on replicating excel reports.I am having trouble setting up a particular calculation.


      To calculate last 4 columns is where I am having trouble, I have taken below steps:

        I have calculated LatestBooksize_RM (this calculates/selects the Latest Outstanding Balance) as shown:

        Now using Another Calculated field LatestBooksize_category I am Categorizing LatestBooksize_RM  as shown below:

        Now to calculate Percentages of client having Latest BookSize less than 5 Cr, etc...for a particular Regional Manager( RM wise) as shown in the excel Graph which is ratio of

      (No of clients having BookSize less than 5 Cr for a Particular RM)/ (Total no. of Clients under RM (1st Column))

        I tried many solutions but none of them is leading me to right calculation.

      Can Someone help me with this calculation. I have attached my workbook(Sheet7 is where I am trying to calculate this last 4 columns).Thanks!!!

        • 1. Re: LOD Expression and Binning of Categorical Value
          Zhouyi Zhang

          Hi, Lihos


          I hope I understand your requirement, below find attached workbook with my solution.


          Below is the screenshot of steps and results.


          Update: forget to mention, I made changes to % calculation as well

          Hope this could help



          1 of 1 people found this helpful
          • 2. Re: LOD Expression and Binning of Categorical Value
            Lihos G

            Hey ZZ,

            This is exactly what I was looking for, Really appreciate your help thanks. I was thinking, since I have Already used LOD expression in Calculation LatestBooksize_RM, Latest_Booksize_Category_% would not Require using LOD again. I Still need to understand LOD expressions I guess.


            I have one more follow up Question.

              I am trying to calculate Difference of LatestBooksize_RM and Booksize value selected by Parameter control which is as follows:

              User inputs number of days and I have to calculate date difference from Latest date and accordingly calculate Difference in Booksize.

            Marked in red is the  same calculation used to calculate LatestBooksize_RM.

            I am able to get Difference Date by taking number of days as input in below screenshot.

            ( variable marked by red arrow is just to test date difference calculation).But I am not able to get the difference in Booksize.I am not sure what I am doing wrong.



            • 3. Re: LOD Expression and Binning of Categorical Value
              Lihos G

              Update : Now I am able to calculate the Latest Value and the Back Date value correctly based on Parameter input .Subtracting both the Values is also giving correct values.