This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Type of file: detailed income statement for one year for entities being subsidiaries of a Holding entity.
Data file: 220 columns (one column for each entitiy per month of the year, plus 1 column for the name of the income statement line item and 2 columns for the line item ID key / account number). 150 rows representing the various income statement line items.
Question: I would like to calculate the total amount per line item for each month (e.g. I need to sum 17 entities columns for each month).
Problem: Using the 'formula' tool takes too much time, since I would need to make a sum-formula 12 times (each month of the year) and for each sum-formula I would have to select the individual 17 columns one-by-one.
What is the fastest way, using the minimum amount of tools possible???
I would look to transpose the monthly fields you referenced so you have a name (Months) and value(Data) field. You can then use the key fields option to group by the name of the income statement, line item etc. This would be the level of specificity you want the summation to be in.
You would then use a summarize tool to group by line item and month and sum the line item fields.
I have attached a rough example using pet store data with monthly sales. Hopefully this can explain the process visually.