I have a use case where I have to perform feedback handling, basically, there is a dashboard published on the server, the user will download the dashboard from the server in the form of an excel file, the downloaded excel file will look somewhat like this, here the last two columns (Manual Status, Manual Settlement Transaction Id) will be blank, here the user will be entering data -
In the above file, the status is unsettled but if the user thinks that it can be settled then he will be entering the details in the last two columns
The user will only be entering details in the last two columns, he thinks that T1, T2, T10 are settled so he has entered settled in the manual status column, now in the manual settlement transaction id column, he has entered transaction id via which it is being settled. Here T1 = T2 + T10 so T1, T2, T10 are settled. Now if this condition holds true then the status should be changed to settled.
Final Output -
Any inputs for the above use case will be helpful, I am trying to achieve this using Tableau Prep but not able to fully achieve the final output.
I can only use Tableau Desktop or Prep for this and not any other language such as Python, R, etc.
Hi Ajay,
Firstly a couple of points - I don't think Tableau will ever output a crosstab dashboard that looks like this, also it would be better practice to do this as a visualisation inside Tableau, rather than producing a crosstab, if this is the ultimate product you need then Excel is probably a better product for you.
My initial thoughts are to confirm if you have considered using the CONTAINS() tag.
For the T1 line;
IF CONTAINS([Transaction ID], [Manual Settlement Transaction ID]) THEN
SUM([Debit])-SUM([Credit])
END
This should give you the balance of the values by looking for T1 in the Manual Settlement Transaction ID field, and then because I aggregated it to SUM it will Add the 450 and 50 together and then subtract that from the 500.
OFC this might then error out because it will then look for T2, then T10 and attempt to do the same equation - this might be resolved either by a data preparation activity, or something you might be able to do inside Tableau Prep.
I'm hoping it doesn't take too much imagination though to consider this as a visualisation. I'd consider trying a Stacked Bar chart in Transaction ID order, with the Debit overlaid with the Credit balance as they come in so that you can visually identify the large gaps. You could tweak the colour by items that have been outstanding for long periods for example to visually identify stale debts. Much better than a crosstab.
HTH
Peter
