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?
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
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.
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.