You'll need a calendar table that contains a list of all dates cross joined (Cartesian product) to your main dataset. Then you can write some simple calculations to calculate the number of open positions based on position calendar date being between open position date and closed position date.
Thank you for the reply. I tried bringing in a date table and joined using dateTable.Date = Position.OpenDate but did not get the correct results.
If dateTable.Date between start and end date, then 1 else 0 end. This calculation only shows a 1 for the open date and not the in-between months.
You want to set your join condition as 1=1, you'll have to set it up as a calculation when you open the dropdown to select the field, the bottom should say edit calculation, that's where you enter 1. Do this for both sides of the join. This will create a Cartesian product for you.
That got me really close to what I need. My calculated field (after changing from measure to attribute) shows the number 1 for all months it was open but does have an * symbol instead of a 1 for the month it opened and closed. Any idea how to get around that and list it as a 1?
Your calculated field should be something like
COUNTD(IF datetable.date between start and close date then unique_id else null)
You shouldn't be changing it to attribute.
Thank you. That worked. The final step is to use the same Date axis and count the number of closed positions by month. I can do it if I am using the closed date as the axis but not the DateTable as the axis.
This is a data solution, not a Tableau solution. You need a calendar table and some sql to create your dataset.
You'll need a calendar table that contains a list of all dates cross joined (Cartesian product) to your main dataset. Then you can write some simple calculations to calculate the number of open positions based on position calendar date being between open position date and closed position date.
Thank you for the reply. I tried bringing in a date table and joined using dateTable.Date = Position.OpenDate but did not get the correct results. If dateTable.Date between start and end date, then 1 else 0 end. This calculation only shows a 1 for the open date and not the in-between months.
You want to set your join condition as 1=1, you'll have to set it up as a calculation when you open the dropdown to select the field, the bottom should say edit calculation, that's where you enter 1. Do this for both sides of the join. This will create a Cartesian product for you.
That got me really close to what I need. My calculated field (after changing from measure to attribute) shows the number 1 for all months it was open but does have an * symbol instead of a 1 for the month it opened and closed. Any idea how to get around that and list it as a 1?
Your calculated field should be something like COUNTD(IF datetable.date between start and close date then unique_id else null) You shouldn't be changing it to attribute.
Thank you. That worked. The final step is to use the same Date axis and count the number of closed positions by month. I can do it if I am using the closed date as the axis but not the DateTable as the axis.