1 2 Previous Next 17 Replies Latest reply on Jul 29, 2016 8:25 AM by Joe Oppelt

    Use a measure for calculation as identified by a dimension of same row

    Michael Moore

      Can i do a calculated field using a measure values as identified via labels from a dimension field?

       

      I have 94 columns per 'Date'. Each represents a different derivative product value trading on the same symbol. Within each date, i'd like to use the Y value (a measure) given a specific X value (a dimension). Then i'd like to create calculated fields comparing the original Y value to other Y values given other specific X values within the same date.

       

      Ive been trying different version of CASE WHEN THEN, as well as IF THEN ESLEIF statements without luck.

       

      Is this a problem I should solve in excel first? or is there a direct tableau solution?

        • 1. Re: Use a measure for calculation as identified by a dimension of same row
          Joe Oppelt

          Are you saying you have a dimension that has numeric values, and you want to treat it as both a Dimension and as a numeric value?

           

          I guess I would need to see a sample data set (wouldn't need a ton of rows, I would hope, excel would be fine) and then you can explain how you want the data to play with itself.  (So to speak.  )

          • 2. Re: Use a measure for calculation as identified by a dimension of same row
            Michael Moore

            No i have a dimension that i use as an identification. It is unique to each row within each 'DATE'. The label is repeated for each date, identifying each row per date as unique. I would like to use that label, to then use the value within a corresponding measure to create a calculated field per date. So... when DIMENSION = 1, then use MEASURE divided by measure when dimension =2.

             

            2016-07-27.png

            • 3. Re: Use a measure for calculation as identified by a dimension of same row
              Michael Moore

              For ex: using the IV values as identified by the OptionID label, id like to do this: (30C35-30C0)/30C0 amongst other relationships.

              • 4. Re: Use a measure for calculation as identified by a dimension of same row
                Joe Oppelt

                I'm still trying to wrap my head around what you are trying to do.

                 

                Are you saying you want to know how to grab the IV value associated with certain OptionIDs?

                 

                Given your example, are you trying to figure out how to get

                 

                (1.0337 - .8204)/.8204

                 

                ?

                 

                 

                How will you know which OptionID values to use?  Will it always be the same formula?  ( (x-y)/y ) ?

                • 5. Re: Use a measure for calculation as identified by a dimension of same row
                  Michael Moore

                  you are 100% correct about the current calculation i am trying to script.

                   

                  All my calculations wont be the same. Sometimes they will be differences, sometimes % differences... but within this data set, i will always use the OptionsID label to identify the IV value to use in calculations.

                  • 6. Re: Use a measure for calculation as identified by a dimension of same row
                    Michael Moore

                    "How will you know which OptionID values to use? "

                     

                    It depends on the relationship I am trying to research. It may be any combination of OptionID's... This is not a create once and use forever solution. I need to know how to create and analyze any relationship.

                     

                    Using a measure associated with a label or date is a normal need that I have though. So far I havent found a great tableau solution.

                    • 7. Re: Use a measure for calculation as identified by a dimension of same row
                      Michael Moore

                      Bump.

                       

                      Can tableau not handle this?

                       

                      I'd love a solution.

                      • 8. Re: Use a measure for calculation as identified by a dimension of same row
                        Michael Moore

                        My current solution is to go to excel, concatenate date and OptionID, then do a massive vlookup on a second page so that i have one row per date, columns per optionsID and cells with the IV input. Not the worst solution, but it feels like a hack...

                         

                        Any better ideas? Or should i award myself the solution check?

                        • 9. Re: Use a measure for calculation as identified by a dimension of same row
                          Steve Martin

                          There is your answer - Excel.

                           

                          We - Joe and silent others (me included) are stuck on your logic.

                           

                          Please can you put this together in Excel, we can then understand the method and help you to build this out in Tableau.

                           

                          Steve

                          • 10. Re: Use a measure for calculation as identified by a dimension of same row
                            Joe Oppelt

                            Michael Moore wrote:

                             

                            Bump.

                             

                            Can tableau not handle this?

                             

                            I'd love a solution.

                            Right now I still don't understand what you are trying to do.

                             

                            You can have a calc that does this:

                             

                            IF [OptionID]= "30C0" then [IV] END

                             

                            Every row that has that OptionID will have its calc set to the value of [IV].  All the rest will be null.

                             

                             

                            How many rows can  have a value of "30C0" ?  If only one, then that's how you can identify the [IV] value you need.

                             

                            You can have a parameter in which the user can enter the OptionID value to use, and instead of hard-cosing a string in the calc, you can use the parameter to yank out the IV value.

                             

                            How will you tell Tableau that for this analysis you want to do (x-y)/y , but for the next analysis you want to do (x*y) - x  ?

                             

                            (Tableau doesn't have a Fn input method like Excel to specify the function syntax.)

                             

                            Will you always have just two IV values you will be manipulating?

                             

                            I can ask a dozen more questions.  There are just so many unknowns in what you are asking that I have no idea where to start suggesting ways Tableau can do what you want to do.

                            • 11. Re: Use a measure for calculation as identified by a dimension of same row
                              Michael Moore

                              Thanks for the thoughtful responses.

                               

                              My data is 10 years of daily market info for about 300 different options on the vix index. Thus, the snapshot above is the first few of 620k rows.

                               

                              My solution in excel (still working on excel from last night due to bugs and constant freezes): step 1) concatenate date + OptionID in main "vix1" sheet. 2) Then on second sheet I use one row only per date. 3) I transposed the 300 options into columns. 4) im using a concatenated Vlookup formula   "=VLOOKUP(CONCATENATE($A3,B$1),'vix1'!$A$1:$K$613783,9,1)" to populate each cell with the appropriate IV...

                               

                              Seems simple, 4 hours later im ready to claw my own eyes out.... (My IV values are not matching, so I am currently am still debugging.)

                               

                              Yes, im pushing the limits of tableau and excel. Excel wants to break every time i copy a new formula to 7 million cells.

                               

                              "How will you tell Tableau that for this analysis you want to do (x-y)/y , but for the next analysis you want to do (x*y) - x  ?"

                               

                              I have no desire to write one script and forget it. The exact relationships that i examine will be dependent on our needs that day. I plan to write new scripts daily to do that.

                               

                              You idea "IF [OptionID]= "30C0" then [IV] END" ... is worth discussing. I tried a few different If/THEN/ELSEIF and CASE/THEN statements w no luck. Can i concatenate date + OptionID in tableau? I thought I couldnt, considering Date is not a string... but what if i change it to a string? If i can concatenate that into one reference column, then can i use  something like " newrelationship = ((IF [OptionID]= "3/24/200630C30" then [IV])-(IF [OptionID]= "3/24/200630C0" then [IV]))/(IF [OptionID]= "3/24/200630C0" then [IV]) ?

                               

                              I realize these are not normal questions, and Im a bit new to tableau... but ive had amazing results using it for options analysis thus far.

                               

                               

                              • 12. Re: Use a measure for calculation as identified by a dimension of same row
                                Joe Oppelt

                                You can concatenate anything. 

                                 

                                STR([Date]) + [OptionID]

                                 

                                Then you would use that calc field in place of [OptionID] itself.

                                 

                                The pseudo-code you proposed would work.  Syntax need work.  (Each IF statement needs an END, for example.)  But yes, you can do stuff like that for sure.

                                2 of 2 people found this helpful
                                • 13. Re: Use a measure for calculation as identified by a dimension of same row
                                  Michael Moore

                                  For serious?

                                   

                                  This is amazing news.

                                   

                                  I'm gonna go try this out. I'll report back what formula works.

                                   

                                  THANKS!!

                                  • 14. Re: Use a measure for calculation as identified by a dimension of same row
                                    Michael Moore

                                    So... ive again been trying a few different variations of if/then/else and case/when statements. i cant get the formula to reference an [Iv] in another row. for the sake of simplicity, ive changed my goal for the moment. all im tryng to do here is take the [Iv] of current row, and compare it to the [Iv] of the at-the-money [Iv] (this is identified by the "0" at the start of the OptionRef for same date). Thus, I am trying this formula:

                                     

                                    If startswith([OptionID], "0") THEN 0

                                    ELSE [Iv]-(If [OptionRef] = "0"+STR([Period])+[Call/Put]+str([Date]) Then [Iv] END)

                                    END

                                     

                                    My second line is an attempt to say take the [Iv] of this option, then "look for the row with the same date/period/call/put as this row, that represents the at-the-money option (starts with "0"), and subtract the [Iv] for that row.

                                     

                                    My results are "0" for every atm option (correct), but "null" for everything else.

                                     

                                    Ive tried this 5 or 6 different ways... i dont think an if/then statement in tableau can reference a different column in the data, afaik. Please advise.

                                     

                                    Thanks.

                                     

                                    Mike

                                    1 2 Previous Next