So I'm stuck with trying to get a percentage breakdown of active accounts with total accounts created. Can't show actual customer data so I'll make a rough representation of the data and the pills type and names relative to the sample data. Date columns are date/time but I didn't get granular in the sample data. I'm trying to create a report to give up to date active users by the date so Week/Month/Qtr/Year with the running total. So far, I can only get the count of active accounts and inactive but no method to get percentages. I would also like to be able just to get a current active user percent.
COLUMN: D(Year(Create Date[customer table])) D(Qtr(Create Date[customer table])) D(MONTH(Create Date[customer table]))
ROW: M(CNTD(Customer ID computed using Table(across)))
COLOR: D(c. Active Cases)
c. Active Cases
IF COUNTD([Case ID]) > [p. Active Case Count] THEN "Active"
p. Active Case Count = 1
I have two tables an order table with an order create date
Table Name: Cases
|CreateDate||Customer ID||Case ID||...|
and a customer table
Table Name: Customers
|Customer ID||Create Date|
In Tableau I have the Customer Table Left Joined to the Case List for the datasource.
So there can be accounts created that have never created a case because they decided to not to create a case.
So this will create records of all the customers at least with the cases but the order create date is null in the query for that customer ID.
To get the dates on the same axis, I also create a new calculation to try and get them to combine better
D(Create Date Merged)
```IF ISNULL([CreateDate (Cases)]) THEN [Create Date] ELSE [CreateDate (Cases)] END```
Now I've been able to get the data get a running total of the accounts.
But when I try to split the data to get a ratio to get the percentages, then that's where it breaks down for me.
I've tried using the lookup function to get the customer ID to "carry over" so when looking at the date I could get results such this so it's possible to sum the data to visualize it but again I'm stuck as I cannot get it to this state. 0 being inactive and 1 being active in status.
|Create Date Merged||Customer ID||Status|
I've looked at all the domain padding, domain completion, and other related kb by Joe Mako and Jonathan Drummey but it doesn't seem to get me there with dimensional only data. The examples only seem to be given with with sales numbers where the data is already in a measure form and where the data has not been aggregated to create a measure to determine the status of the account at that date column.
Message was edited by: Paul Wanless I added a sample workbook of the data to look at with some of the key calculations that had been tried out to try and get it to work along with some views.
demo_book.twbx 321.6 KB