4 Replies Latest reply on Dec 13, 2016 4:24 PM by Shinichiro Murakami

    Need help on LOD calculation

    ranjit saha

      Hello,

      Stuck up with a typical calculation that am not not able to get around.

      Please do refer to the excel for the data.

       

      Sheet 1: has the list of customers with multiple sales in one bill(or each line). each sales in a line will amount to a fee depending on the combination matrix given in sheet2

      e.g. in the line1, customer 1122, made 3 purchases, total commission charged is 1000, now we need to check how many individual sales are there in the line and the sum of each of the commission amount as per the sheet 2.

       

      aim is to verify, if the commission fee paid was more than the contracted commission fee.

        • 1. Re: Need help on LOD calculation
          Shinichiro Murakami

          Ranjit,

           

          I don't know why you mentioned LOD in title, anyways..

           

          Assuming you have version 9.3 or newer.

          Create union with dragging sheet 1 three times.

          sheet1, 11, and 12 are there.

           

           

          [Date union]

          if [Table Name]="Sheet1" then [date1]

          elseif [Table Name]="Sheet11" then [date2]

          elseif [Table Name]="Sheet12" then [Date3]

          end

           

           

          [From to union]

          if [Table Name]="Sheet1" then [from Co-Ordinates 1]+" to "+[to cordinates1]

          elseif [Table Name]="Sheet11" then [from2]+" to "+[to2]

          elseif [Table Name]="Sheet12" then [from3]+" to "+[to3]

          end

           

           

          Fee table as secondary data source(=Master).

           

          [From to]

          [Co-ordinate 1]+" to "+[Cordinate 2]

           

           

          Link two data source with from to

          From here, it's not so hard I believe.

           

          Thanks,

          Shin

          • 2. Re: Need help on LOD calculation
            ranjit saha

            Hello Shin,

            Actually am working on 9.2, and i dont find the Union option here

            • 3. Re: Need help on LOD calculation
              ranjit saha

              Hello Shin,

               

              Any luck with is issue.

               

              Thanks,Ranjit

              • 4. Re: Need help on LOD calculation
                Shinichiro Murakami

                It was not easy.

                I STRONGLY recommend that you install 9.3.......

                 

                 

                === data 1==

                [Event Category]

                "Event"+right([Pivot field names],1)

                 

                [Field Category]

                if contains([Pivot field names],"rom") then "From"

                elseif contains([Pivot field names],"To") then "To"

                elseif contains([Pivot field names],"Date") then "Date"

                end

                 

                [Date]

                {fixed [Customer],[Event Category]:

                min(if [Field Category]="Date" then [Pivot field values] end)}

                 

                [From to]

                {fixed [Customer],[Event Category]:

                min(if [Field Category]="From" then [Pivot field values] end)}

                +" to " +

                {fixed [Customer],[Event Category]:

                min(if [Field Category]="To" then [Pivot field values] end)}

                 

                [Filter out Null]

                str([Customer])+str([Date])

                 

                == Fee table ==

                [From To]

                [Co-ordinate 1]+" to "+[Cordinate 2]

                 

                 

                 

                Thanks,

                Shin