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.
I'm having trouble with using the cross tab to create a trend analysis. The first column contains the names and the second column contains all the dates and values. To put it visually, see below. Take note that it's just the sample
Name | Value - Header Row Field | 1/2/2019 - 1st row
Jay | 1000
Spence | 123
Reese | 478
Field | 1/3/2019
Jay | 1100
Spence | 145
Reese | 528
Ruth | 900
Field | 1/4/2019
Ruth | 850
Spence | 500
As you can note, the data is indefinite, and there's no work around there since the source file is being maintained by someone else. So, I think the next steps are to create a new column with the dates only per row, so that I can use the cross tab to group it. I have no idea on how to create that new column with just the dates indicated per row.
Please let me know if this makes sense or if I need to explain further. Would appreciate any help on this.
You can use a Multi-Row Formula tool and an IF statement to create this new column
IF [name]="Field" THEN [Value] ELSE [Row-1:Value] ENDIF
This will create a new column and only add in the value if it has the date field associated with it - all of these have the first column as 'Field'. Then for all of the blank rows it will then go and reference the date from the field above.