6 Replies Latest reply on Jun 2, 2016 3:07 PM by Rohit Garg

    Calculate difference of value 1(Sheet 1) and value 2(Sheet 2) in Sheet 3

    Rohit Garg

      Hi, I am looking to calculate difference of values calculated on two sheets (with same data-source) in the third sheet. I have 2 parameters - Date Range (Day/Week/Month/Year) and Measures (Units, DRYS/Unit, Revenue, $ per DRY). Common calculated fields:

       

      Calc_Date Display:

      IF [Date Range] = "Day" THEN DATETRUNC('day',[Order Date])

      ELSEIF [Date Range] = "Week" THEN DATETRUNC('week',[Order Date])

      ELSEIF [Date Range] = "Month" THEN DATETRUNC('month',[Order Date])

      ELSEIF [Date Range] = "Year" THEN DATETRUNC('year',[Order Date])

      END

       

      Calc_Measures to Display:

      CASE [Measures]

      WHEN 'Units' THEN SUM([Product Unit Qty])

      WHEN 'DRYS/Unit' THEN sum([Calc_DRYS])/sum([Product Unit Qty])

      WHEN 'Revenue' THEN SUM([GCR Amt])

      WHEN '$ per DRY' THEN SUM([GCR Amt])/SUM([Calc_DRYS])

      END

       

      Sheet 1 (Current Term)

      • By current term, I mean - If I select 'Day' in Date Range parameter then the sheet should show me Today data. I am able to achieve this with the help a calculated field -

           Calc_Current Term

       

           CASE [Date Range]

           WHEN 'Day' THEN IF DATETRUNC('day',TODAY()) = DATETRUNC('day',[Order Date]) THEN "Show" END

           WHEN 'Week' THEN IF DATETRUNC('week',TODAY()) = DATETRUNC('week',[Order Date]) THEN "Show" END

           WHEN 'Month' THEN IF DATETRUNC('month',TODAY()) = DATETRUNC('month',[Order Date]) THEN "Show" END

           WHEN 'Year' THEN IF DATETRUNC('year',TODAY()) = DATETRUNC('year',[Order Date]) THEN "Show" END

           END

       

           This is how it looks like:

       

      Sheet 2 (Previous Term)

      • Similarly I have a sheet 2, where in I have only 1 difference i.e. Calc_PreviousTerm to show previous day data.

           CASE [Date Range]

           WHEN 'Day' THEN IF DATETRUNC('day',DATEADD('day',-1,TODAY())) = DATETRUNC('day',[Order Date]) THEN "Show" END

           WHEN 'Week' THEN IF DATETRUNC('week',DATEADD('week',-1,TODAY())) = DATETRUNC('week',[Order Date]) THEN "Show" END

           WHEN 'Month' THEN IF DATETRUNC('month',DATEADD('month',-1,TODAY())) = DATETRUNC('month',[Order Date]) THEN "Show" END

           WHEN 'Year' THEN IF DATETRUNC('year',DATEADD('year',-1,TODAY())) = DATETRUNC('year',[Order Date]) THEN "Show" END

           END

       

      Problem: I want to show difference of values calculated on sheet 1 and sheet 2 for identical parameter values(Ex - For Day and Units combination, I would like to see difference of sheet 1 value and sheet 2 value) in sheet 3. I would really appreciate if someone could please assist me.

      P.S. I have tried searching the forum, but couldn't find anything relevant. If it's discussed already, please share the link.Esther AllerJonathan DrummeyNicholas Hara

        • 1. Re: Calculate difference of value 1(Sheet 1) and value 2(Sheet 2) in Sheet 3
          Nicholas Hara

          Hi Rohit,

           

          Thank you for tagging me in your post, I appreciate that you trust my expertise. It is, however, seen as poor etiquette to tag multiple people within a post, unless there is a specific ask for their area of expertise. I'd ask that in the future you please limit your tagging to relevant and essential circumstances. Doing so ensures that you get a good response when tagged and that our expert community members are utilized for their specific skills.

           

          Anyways, I took a quick look at the problem above and I would suggest the following approach:

          1. Revamp your calculations to be less verbose

          2. Use a period over period quick table calc instead of attempting to compare two sheets.

          3. Hide your empty value

           

          1. Revamp your calculations

          When using a parameter to select different date levels, I always inject the parameter into the DATETRUNC() calc instead. Your new calculation for your current and previous periods should look something like this:

          DATETRUNC([Date level Parameter],DATEADD([Date level Parameter],-1,TODAY()))>=DATETRUNC([Date level Parameter],[Date])

           

          This creates a boolean that you can use to show the values for both the previous period and the current

           

          2. Build your view

          a. Add your Date field to the Columns shelf, Make it MDY.

          b. Add the new Boolean calculation to your filters shelf and filter to TRUE

          c. Add the Measure to Display calc to the Text shelf

          d. Right-click on the Measure to Display> Quick Table Calculation > Difference

           

           

          3. Hide the empty values

          a. Right-click on the Measure to Display>Filter

          b. Select Special> Non-null values

           

          Hopefully this helps!

          1 of 1 people found this helpful
          • 2. Re: Calculate difference of value 1(Sheet 1) and value 2(Sheet 2) in Sheet 3
            Rohit Garg

            Thank You  Nicholas Hara  for your detailed response and letting me know about best practice for tagging people. I will keep in mind.

             

            Regarding the solution you suggested:

             

            1) I created a calculated field, which looks like this:

             

            DATETRUNC([Date Range Poc],DATEADD([Date Range Poc],-1,TODAY()))<=DATETRUNC([Date Range Poc],[Order Date])

             

            2) Built view

            a. Added date field, made MDY.

            b. Added Boolean calculation

            c. Added measure to display text shelf

            d. Added Quick Table Calculation > Difference

             

            3) Hide empty values

             

            This logic works perfectly when I have 'day' selected as parameter value. However, when I select 'week', 'month', 'year then, data (Difference of values) is displayed for last week, month, year respectively. Instead, I want to show week over week, month over month, year over year difference. Please refer:

            Day

            @

             

            Week

             

            Month

             

            Looking forward for your response. Thank You !

            • 3. Re: Calculate difference of value 1(Sheet 1) and value 2(Sheet 2) in Sheet 3
              Nicholas Hara

              Hi Rohit, that was my fault.

               

              You need to create a custom date that is responsive to the parameter as well. (facepalm)

               

              Instead of Order Date use a date field with the following calculation: DATETRUNC([Date Parameter],[Order Date])

               

              Hopefully this helps!

              • 4. Re: Calculate difference of value 1(Sheet 1) and value 2(Sheet 2) in Sheet 3
                Rohit Garg

                Thank You Nicholas Hara. That worked

                 

                I have to ask you one more thing -

                 

                Scenario:

                1) Month - May (M1/current month), April (M2), March (M3)

                2) Week - Week starting: 23rd May (Week 1/current week), 16th May (Week 2), 9th May (Week 3)

                3) Day - 24th May (Day 1, current day), 23rd May (Day 2), 22nd May (Day 3)

                 

                If I want to show difference of M2 Vs M3, Week 2 Vs. Week 3, Day 2 vs Day3. Then, what shall I do different?

                 

                I tried modifying the field you mentioned earlier as follows: DATETRUNC([Date Range Poc],DATEADD([Date Range Poc],-2,TODAY()))<=DATETRUNC([Date Range Poc],[Order Date])

                I get following:

                 

                    

                Goal: I want to show difference value only for 23rd of May(day), 16th May(week),  1st April(Month).

                 

                Thank You for your continued support.

                • 5. Re: Calculate difference of value 1(Sheet 1) and value 2(Sheet 2) in Sheet 3
                  Nicholas Hara

                  Hi Rohit,

                   

                  I'm glad I resolved your initial question. Be sure to mark the answer as correct so that other community members with similar questions can search for and find the answer.

                   

                  As for your new question, I think in order to change the two periods that are being compared, you can use a string parameter ([Period]) to dictate the range.

                   

                  DATETRUNC([Date Range Poc],DATEADD([Date Range Poc],-([Period]+1),TODAY()))<=DATETRUNC([Date Range Poc],DATEADD([Date Range Poc],-[Period],[Order Date])

                   

                  AND

                   

                  DATETRUNC([Date Range Poc],DATEADD([Date Range Poc],-([Period]),TODAY()))<=DATETRUNC([Date Range Poc],DATEADD([Date Range Poc],-([Period]-1),[Order Date])

                   

                   

                  When you enter a number, it will now look at the 2 periods that you dictate.

                  • 6. Re: Calculate difference of value 1(Sheet 1) and value 2(Sheet 2) in Sheet 3
                    Rohit Garg

                    Hello Nicholas Hara, Hope you are doing well. I am trying to implement an additional functionality i.e. Year over Year percentage difference in metrics (Calculated field - Calc_Measures to Display, as mentioned above). However, % difference is being calculated with respect to previous value(Ex - % diff of May(2016) and April(2016) value). Please refer:

                     

                    Where, Calc_Time_Filter is defined as follows:

                     

                    If [Date Range]="day" AND DATEDIFF('day',[Order Date],TODAY())<28 THEN "Show"

                    ELSEIF [Date Range]="week" AND DATEDIFF('week',[Order Date],TODAY())<15 THEN "Show"

                    ELSEIF [Date Range]="month" AND DATEDIFF('month',[Order Date],TODAY())<13 THEN "Show"

                    ELSE "Hide"

                    END

                     

                    GOAL: I want to show % difference between YOY value. For Ex - % diff between May(2016) and May(2015). Please refer (This chart contains same data):

                    Waiting to hear from you.