5 Replies Latest reply on Sep 20, 2019 8:45 AM by Jim Dehner

    LOD Expressions using multiple data sources

    Touseef Patel

      Hi All,

       

      I am trying to create a calculated column with a LOD expression using a variable from other data source. I am sure this can be done by having a union of the data. but the way original data is structured is very complicated and a union will add more complexity. I also want to keep it automated when the input data file is updated, so having a filter or parameter does not solve that problem either.

       

      So, in the dummy data, there are two tables from two data sources.

      - Data to Map from

      - Data to Map to

       

      What I'm trying to do is, create a new column in the 'data to map to' table, saying I only want to see 'PY Sales' numbers where the 'Period' column is equal to the MAXIMUM of 'Sales Cycle' column from 'Data to Map from' table, which is from of another data source. In short, this will act like a filter to the 'Data to Map to' sheet, and give only P07 data, as it the maximum value in the 'Sales Cycle' column.

       

      The formula that I am tried is shown in the image below and is:

       

      IF [Period] = {MAX([Data to Map From (Tableau Dummy)].[Sales Cycle])}

      THEN [PY Sales]

      ELSE 0  END

       

      This formula worked for me if when I use a variable from the same data source in the {}. The data sheets exist in the same excel workbook but it is best to not have any sort of union and that is why I am trying to solve it this way.

       

      The result should only give me PY Sales values in the rows where the period is P07, MAX of Sales Cycle column. Can some genius tell me how this be done?

       

      Please let me know with any suggestions or tricks with which I can accomplish the task I am trying to do!

       

      Attached version is 10.2.twbx

       

      Thanks in Advance,

      Touseef

       

        • 1. Re: LOD Expressions using multiple data sources
          Jim Dehner

          Hi

           

          sorry - you can't create a an LOD across blended data sets - they just don't work that way

          so you are back to a union and parameter

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: LOD Expressions using multiple data sources
            Touseef Patel

            Is there any other way for me to solve this problem? Like have only periods where it matches the maximum of sales cycle?

            • 3. Re: LOD Expressions using multiple data sources
              Jim Dehner

              as long as you blend the data sources you will not be able to use lods or cross db filter

              I looked at your data and assume that your real data is more extensive - - if you are just looking at 10 periods and values - you could hard code it into a conditional statement - maybe a case  when period p10 then value 1 when p02 then value 2   etc

               

              Jim

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              • 4. Re: LOD Expressions using multiple data sources
                Touseef Patel

                In the original data - the unique number of periods are close to 10. Can you give me the conditional code that solves the purpose? A code even for 5 periods will be fine. I just want to understand what you are trying to say.

                 

                What I understand by your last sentence is just the change in the value based on the value in the Period column, but how do I condition the code to pick the maximum of Sales Cycle from 'Data to Map from' table and check it against the Period Column. Hard coding the values will be okay as there are not many unique values in the Period/Sales Cycle column.

                 

                Will be really helpful if you can give me a code!

                 

                Touseef

                • 5. Re: LOD Expressions using multiple data sources
                  Jim Dehner

                  understand

                  you are doing this because you have chosen to NOT do the most conventional solution

                  that results in a brute force way to accomplish the goal

                   

                  you have a PY file that contains data - just create a statement similar to

                   

                   

                   

                  then you can use it by itself or in other calcs like

                   

                   

                  the reason I use MIN()   is it will aggregate - if there are more than a single record eg it will sum

                   

                  it returns this

                   

                   

                   

                   

                  NOTE - I would not do this with a client - if the PY file has any size then the case statement gets out of hand - I would just unions or joins in the data

                   

                  Jim

                  If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.