4 Replies Latest reply on Apr 3, 2018 5:13 AM by Danilo Cardoso

    Diff between values of one date parameter vs a date range set by two parameters

    Danilo Cardoso

      Hi folks, I have a sales data base with dimensions of sales, category, region, channel, customer and date (months).


      What I'm trying to achieve is a calculation of the difference between the sales in one month (determined by parameter 1) and the average sales of a set of months (determined by parameters 2 and 3)


      The formula I thought that would do trick was:


      SUM(IF [Date] = [parameter1] THEN [Sales] END) -

      (sum(IF [Date] >= [parameter2] and [Date] <= [parameter3] THEN [sales] END) / (ABS( DATEDIFF( 'month', [parameter2], [parameter3]) ) +1 ))


      Unfortunately, it ends with null results. I can't figure out why this happens since I use the same formula only replacing the minus with a division to get the results of percentage of growth between the chosen months.


      I think I got the solution but I don't understand why it has be like that. I got the right results by introducing a FIXED with all dimensions before the expression above.


      {FIXED [category],[channel],[customer],[region] | expression above }


      Anyone can explain to me why it has to be like that? Is it correct like that? I can't understand why INCLUDE don't give me the same results also...

        • 1. Re: Diff between values of one date parameter vs a date range set by two parameters
          Danilo Cardoso

          Another intriguing question...


          The calculated field that I mentioned above is in a simple chart that has DATE on the page shelf.

          Why the **** when I change the month in the page shelf the results also change if the calculated field uses only the values of fixed date parameters?


          In other words: if parameter 1, 2 and 3 are untouched why the results change when DATE is changed on the page shelf?

          • 2. Re: Diff between values of one date parameter vs a date range set by two parameters

            I have a small issue with the editing relationship between same data sources.

            I have two custom sql queries in two different sheets. Both the custom sql queries come from the same data source.  I cannot join the two queries because of many to many relationships.  So here is the issue, I have one table (table 1)  that has year starting from current year July to next year June calling it a Fiscal year. So, the one query which already has the fiscal year is good, and I can pull employee’s salary paid within that fiscal year. The other table, which is a little corky, is the table ( table 2), which has the salary changed as on an effective date. Meaning, if there has been any change in the salary due to fulltime/parttime  or change of position, there is a change in the salary and only that effective date is captured. I sorted out a couple of issues and got the two custom queries ready. So I am trying to join the Fiscal year from one table( table 1) to the Fiscal year  which is a calculated field based on the effective dates in the other table (table 2) .

            Now I am creating a dashboard and I need a single option to click within the filters and when I do, I should have data for that one fiscal year. I have done that for employee ID that is common ID in both tables, which came from the tables within the custom sql queries by using Data blending and editing relationships. I am unable to join the Fiscal year and I don’t know why . Do you have a solution ? I am working on Tableau  10.5




            • 3. Re: Diff between values of one date parameter vs a date range set by two parameters
              Peter Fakan

              Hi Danilo,


              I'm not 100% sure of your solution as well, but I have previously taken a different approach to obtaining a similar result;


              Step 1 - create parameters for dates

              Step 2 - create calculated fields that refer to parameters (i.e. if [Year] = [Parameter] then ... END)

              Step 3 - create calculated fields to derive date calculations using Step 2 calculations (i.e. sum([calc1 with first date]) - sum([calc2 with 2nd date])


              This seems (to me) to get around any complex logic issues with combining lookups and dates within the same calculated field if they are separated through using different calculated fields for each part of the calculation.





              • 4. Re: Diff between values of one date parameter vs a date range set by two parameters
                Danilo Cardoso

                Thanks for the response Peter.


                Your answer makes the formulas simpler but don't exactly act on the solving of the problem.


                I have made some progress in solving the issue but the major question has changed.

                What I discovered is that what was making the expression below display null values is the DATE dimension inside the PAGES SHELF.


                SUM(IF [Date] = [parameter1] THEN [Sales] END) -

                (sum(IF [Date] >= [parameter2] and [Date] <= [parameter3] THEN [sales] END) / (ABS( DATEDIFF( 'month', [parameter2], [parameter3]) ) +1 ))


                If I take DATE out of PAGES then it works fine.


                Now the questions are:


                1 - Why DATE on PAGES SHELF mess with expressions that have DATE fixed by a parameter?

                2 - Is there any workaround for this or I have to get rid of DATE on PAGES SHELF?****


                ***Why I'm using DATE on PAGES if my chart don't need it? Because I made a double chart!

                The upper part is the sum(SALES) for the dimension in the month chosen by DATE on PAGES

                The lower is the expression above that only correlates to the dates defined by the parameters.

                (PICTURE BELOW)