That worked fine but when I used the calculation again my production data, i'm getting negative [Days with Assets] and not sure why these are appearing as the dates look fine.
I have attached the spreadsheet with negative days.
negative_dayswithassets.xlsx 19.3 KB
Calculation logic reorganized.
But one question,
How would you calculate "Days with Assets" for this line?
Prop Code Property Month End Void Start Date Accepted by Assets Referred to Allocations Accepted by Allocations Let Date 3611 2015/08/31 2015/07/29 2015/08/19 2015/08/09 2015/01/09 2015/09/15
Sample_Vacant_Days5.twbx 100.5 KB
Since the logic is complicated, some of the if/iif in the calculation is written to match certain rows.
I'm not sure how the modification will affect the result of other rows.
Please check the calculation on your whole data as carefully as possible.
Sample_Vacant_Days5.twbx 91.8 KB
Yeah it is a bit complicated and still getting negatives and wrong day counts. By the way, do we really need to compare [Accepted by Assets], [Referred to Allocations] and [Accepted by Allocation] to [Void Start Date].
For example what if we check [Accepted by Assets] is in between Month Start and Month End date. If it is we date diff between [Accepted by Assets] - [Referred to Allocations].
Will this logic work?
I have posted the same query on the SQL forum and getting more accurate results using the SQL query. Still like to achieve the same in Tableau. Have a look at the link below and hope this helps.
I think it's time to review the total logic
- Check null date
1st condition: is [Void Start Date] null
Yes -> 0
No -> continue with the 2nd condition
2nd condition: is [Accepted by Assets] null? (Here, [Void Start Date] not null)
Yes -> continue with the 3rd condition.
No -> continue with the 5th condition.
3rd condition: is [Referred to Allocations] null? (Here [Accepted by Assets] null, [Void Start Date] not null)
Yes -> continue with the 4th condition.
No -> not defined.
4th condition: compare [Void Start Date] and today
Void > today -> 0
Void <= today -> date difference from Void to today.
5th condition: is [Referred to Allocations] null? (Here, Neither [Accepted by Assets] or [Void Start Date] is null)
Yes -> continue with 6th condition
No -> all the three days not null case
6th condition: compare today with max([Void Start Date], [Accepted by Assets])
today < max -> 0
today >= max -> date difference from max to today.
Sorry that I don't have time from now on today, so I'd like to continue with the "all the three days not null case" logic tomorrow.
Before that, please confirm the above "check null date" logic.
For the "all the three days not null case", I tried hard to make the logic clear, but to find out that there are too many cases.
There are six dates affecting the calculation,
1) Month Start
2) Month End (=Property Month End)
4) Void Start Date
5) Accepted by Assets
6) Referred to Allocations
Assumed 4=5 and 4<6, there have already been 21 possibilities.
There are too many permutations with them all.
In the calculated field for days with assets, I only listed the cases you listed before, while more cases are not defined or not defined precisely,
which results in some results not correct while testing production data.
My advice you that,
1) make a list of all the possible permutations
2) filter out the not existing permutations
3) figure out the calculation logic for each permutation
Then we can start writing Tableau calculation field.
Apologies for coming back to this after a long time. As per the above request the below are possible permutations:
Flow of property movement: Void Start Date > Referred to Assets> Referred to Allocations>Let Date
Case 1: All the dates can be the same if the property is repaired and let same day. In this case property was with assets and allocation 1 day
Case 2: Referred to Assets & Referred to Allocation can be greater than void start date
Case 3: Void Start Date & Referred to Assets are same day and referred to allocation is greater than Void Start Date & Referred to Assets
Case 4: Let date can be blank for few months. In this case Days with allocation is the full month or Referred to Allocations - End of Month
Case 5: When Referred to Allocation day is within the month and Referred to Assets date is in the previous month and let date is blank
a) Days with assets is Start Month - Referred to Allocations
b) Days with Allocations is Referred to Allocations - End of Month
I have attached sample workbook and desired result in the excel file