1 of 1 people found this helpful
However because some people may never transact with us again and hence not appear in the resulting line chart at all, I can’t find a (easy) table calculation that will do this.
If I understand your goal correctly, it seems like this is the key problem: You can't find the the total number of interventions from the transaction data alone. ...
However, you can find the total number of distinct people who transacted and where "intervention performed" = yes with with something like,
IF [Intervention performed] == "YES"
THEN [Customer ID]
You must have another table that contains customer ID and intervention date? You could add this as a data source and use data blending on date to combined these into one view.
Thanks very much for your reply, sorry it took me a while to come back.
You identified precisely the main problem, and your solution makes sense.
Unfortunately due to the (strange?) way our data is structured, the intervention dates are in the same table. They are triggered as a result of a previous transaction, so in reality I rather over simplified the example above, my apologies. It would look more like:
Customer ID Transaction ID Transaction date Intervention performed in past Months since last intervention New intervention triggered Intervention date 123456 1 1/1/2013 YES 20/1/2013 123456 2 20/2/2013 YES 1 NO n/a
Anyway, you inspired me to duplicate the datasource, thinking that your idea would work if I did it on the secondary datasource. However I'm finding that Tableau won't let me use (even drag and drop) fields from the duplicate datasource into the analysis I'm doing using the original data source. The field list is entirely grayed out and has an orange line up the side of it. Do you know if that is normal, or am I doing something wrong?
My data source is a Powerpivot, which is a type of cube, so I won't if it's anything to do with Tableau's limits on OLAP data sources.It's not mentioned at http://kb.tableausoftware.com/articles/knowledgebase/functional-differences-olap-relational so perhaps I am just missing a trick. Any experience with duplicate datasources?!
Thanks again for your information so far.
Glad to have helped with inspiration . I apologize for not seeing the reference to PowerPivot.
I'm still a little confused as to whether you have rows for all customers who had an intervention, regardless of whether there was a subsequent transaction / transaction ID? This is the count you need for the denominator, total customers with intervention.
I really don't have much experience with OLAP, but my initial thought is that this count needs to be generated in PowerPivot or via an MDX query from Tableau.
If you were using a relational database and if you had rows in your table for all customers who were interviened, you'd do something like
IF [New Intervention triggered] == "YES"
THEN [Customer ID]
Total allows you to aggregate at a different level (all dates) than the partitions in the view. A duplicate data source shouldn't be required.
But again, I'm not sure whether you can do this with an OLAP data source. Maybe you want to repost your question with OLAP in the title and someone may pick it up with experience with OLAP.
Sorry I couldn't be of more help.