1 Reply Latest reply on Dec 9, 2016 5:09 AM by Mahfooj Khan

    Need help to create a single field based on two fields.

    kamal kumar

      Hi,

       

      I have a sample data which consists of 2015 and 2016 dimensions and 2015 sales and 2016 sales.

      I want to create another dimension named test by combining 2015 and 2016 dimensions. When we drag test dimension and 2015 sales it should show the exact values like 2015 dimension and 2015 sales and also when combined test dimension and 2016 sales it should show values like 2016 dimension and 2016 sales. I am attaching the sample workbook with data and also individual sheets which has 2015 dimension with sales and 2016 dimension with sales.

       

      Basically, I want to achieve diff of sales(2016-2015) with test dimension

       

      Ex: In 2016 dimension, Furniture has sales of 2621475486 and in 2015 dimension,Furniture has sales of 2756932032. When we create test dimension by combining 2015 and 2016 dimension, Furniture should show the difference of 2016 and 2015 Sales which is -135456546.

        • 1. Re: Need help to create a single field based on two fields.
          Mahfooj Khan

          Hi Kamal,

           

          You've to work with the format of your data or you can use Custom SQL to achieve the output.

          Find my approach, I've connected your excel with legacy connection used custom sql query to transpose your data. Doing that I can get the Category and their sales by year.

           

          select * from

          (SELECT [2015] AS Category,[Measure 2015] AS [Sales], "2015" AS [Year] from [Sheet1$]

          union

          SELECT [2016],[Measure 2016], "2016" from [Sheet1$])

          where [Category] is not null

          Then go to worksheet and drag the fields like that. Create a calc field to get the difference

          ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)

          Hope this help. Let us know If you've any query. workbook (version 9.3) attached for your reference.

           

          Mahfooj

          1 of 1 people found this helpful