Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have the following table:
My goal is to have all this information in one row. First column has the same information (project name and customer name). How do I achieve this?
Thank you in advance.
Kind regards,
Simon
your table rows will naturally aggregate to common dimension values,
your revenue numbers are aggregating on different rows which indicates that your dimension values are not the same
If I am understanding correctly you want the totals of the values for the first several columns. Not sure how you wanted to handle the last several. So the following code illustrates two options for those last fields and pulls the maximum string value from the Incoterm field, but concatenates the distinct values from the Country field. Here is the code and screen shot of what the combined values would look like.
TempData:
Load * Inline [
Customer, SalesPrice, Commission, AddWork, Revenue, Incoterm, Country
Customer1, 158, 0, 0, 158, FCA, ROU
Customer1, 0, -24, 0, -24, , ROU,
Customer2, 160, 10, 25, -10, WHO, FRA
Customer2, 200, 35, 50, -35, OTHER, SP
];
Data:
NoConcatenate Load
Customer,
Sum(SalesPrice) as Total_SalesPrice,
Sum(Commission) as Total_Comission,
Sum(AddWork) as Total_AddWork,
Sum(Revenue) as Total_Revenue,
MaxString(Incoterm) as Max_Incoterm,
Concat(Distinct Country, ', ') as Combined_Country
resident TempData
Group By Customer;
But if I understand correctly I have to fill everything in manually?
your table rows will naturally aggregate to common dimension values,
your revenue numbers are aggregating on different rows which indicates that your dimension values are not the same
No you don't have to fill in the values manually. My sample code can be copied and pasted as is. I used various aggregation functions to illustrate that you can do group by statements to aggregate values up as needed.
You were right, I corrected a few formula's and now it is all in one row. thank you!