3 Replies Latest reply on Apr 29, 2016 7:33 AM by Simon Runc

    Dimension calculation help required

    sreekanth.upputuri

      Hi Team ,

       

      we have the following "Additional Text" column and need to create calculated measure based on this attribute to get data like in below snapshot. Please let us know the possibility

       

      for ex..100 Pack is 100

      3 Full + 3 upgrd promo is 6 like all the columns measure required

      1.png

        • 1. Re: Dimension calculation help required
          Simon Runc

          hi Sreekanth,

           

          I'm prettry sure we can do this (either using the Split function, or RegEx...). Just a couple of questions...

           

          Are you using a TDE or Live connection? (just need to know if you have RegEx support...so I know the tools at my disposal!)

          Will there only ever be 2 numbers to add up? (i.e. you never need 'Promo 6 + 1ASTE + 2XYZ')

          If there are 2 numbers to add, will there always be a'+' sign?

          Finally, can you copy and paste the above image as text...so I can work on your actual entries.

           

          Thanks

          • 2. Re: Dimension calculation help required
            sreekanth.upputuri

            we are using Excel live connection . also can you please give me an example calculations

            • 3. Re: Dimension calculation help required
              Simon Runc

              hi Sreekanth,

               

              So good news is RegEx is supported with Excel...as you didn't send me over the text strings you have I've typed up a few from your image

               

              Please find attached. In essence I've checked for a '+' sign and used the split function to split the string in 2 if there is. I then use RegEx to extract the numbers from the string and then add them up.

               

              This isn't perfect (example the 2012 comes out) but as you'll see it's 99% there. Not sure if you know RegEx (if not here is a handy link to practice and check the statements RegExr: Learn, Build, & Test RegEx...where you can put some examples in the main box and then see what different RegEx expressions will do). I'm still learning myself, so if you know all your entries/rules [eg. don't capture 2012] you can put this in an build up the expression. It's definitely a skill worth pursuing!!

               

              hope this helps.