Forum Discussion
How to connect values to specific date columns
Hi all,
I have the following table - all data is raw data, no calculated fields as of right now:
I have unpivoted the table according the 8 different columns:
4 Invoice Amount (currency)
4 Collected Amount (currency)
There are correspondingly:
4 Invoice Date columns
4 Collected Date columns
The reason for this is that each project has up to 4 different invoices/collection amounts with dates for each of these.
Now I wish to be able to make it so that:
-"Collected 1" (currency) connects to the "Collected Date 1",
-"Invoice 1" (currency) connects to the "Invoice Date 1",
"Collected 2" (currency) connects to the "Collected Date 2",
-//-
-//-
......
In other words I would like to track what is being invoiced in which months and likewise what is collected for each month.
I am trying to create a "Clustered Column Chart" in which the Invoiced/Collected Amount shows by Month.
So in that example I would have a graph that shows the following:
January 2020:
Invoiced Amount: 90.000
Collected Amount: 40.000
February 2020:
Invoiced Amount: 40.000
Collected Amount: 90.000
March 2020:
Invoiced Amount: 40.000
Collected Amount: 40.000
6 Replies
Anonymous
Okay I think I am getting closer to a working solution. I have done the following:
Made two duplicates of the original table and called one "Collected-Table" and the other "Invoiced-Table".
Now I have deleted all the Invoiced Date and Amount from the "Collected-Table" and deleted all the Collected from the "Invoiced-Table".
Now I have Pivoted the Dates columns as well as the collected/Invoiced Amount in each of the tables.
However, now I see one issue:
IF a project has more than one collection, then the "Value" gets duplicated. I guess I can use the "Remove duplicates" from the "Value column, but what happens then if at one point a collection matches a collection from a different project? Then the data would not show?
Hmm, the collected values of 1 project will allways be the same? Like:
Project 1 > Collected 1 (date) 5000 > Collected 2 (date) 5000?
If do. It is not a big problem to leave it. Else you have indeed a chance to delete data you do not want to delete. You can easily get the MAX,MIN,DISTINCT or whatever value of that project, because it will allways be the same.