Get Row Values as Field Nams

Question

Hi Experts,

I have a fuzzy question about reframing table.

I was wondering is her any easier way to get the changes as following:

Customercurrent dateoperating incomeliquidityother income116-Dec10003004117-Dec20004005116-Jun30005006117-Jun40006007118-Jun500070010216-Dec500451217-Dec7501224216-Jun9005325217-Jun1401326218-Jun900127

customer16-Dec Operating Income16-Dec Liquidity16-Dec Other Income17-Dec Operating Income17-Dec Liquidity17-Dec Other Income16-Jun Operating Income16-Jun Liquidity16-Jun Other Income17-Jun Operating Income17-Jun Liquidity17-Jun Other Income18-Jun Operating Income18-Jun Liquidity18-Jun Other Income1100030042000400530005006400060075000700102500451750122490053251401326900127

I want to calculate Last 12 Month Value, however, the data is not by days, but as Year To Day. I want to do the transformation because I think Multi Fields Formula could make it.

Is there any other recommendation on that?

Thanks a lot.

JohnJPS · Accepted Answer

Hi @CrayonZac,

Sure, it's sort of a partial transpose, so I would approach it with transposing with key fields Customer and current date.  Then combine fields current date and "name" from the transpose.  Finally, cross tab back, grouping by customer, and your combined field from the last step as the header, and the value from the transpose as, still, the value. See attached workflow for exact solution.

CharlieS · Accepted Answer

A little bit of transformation is needed to make this happen. Check out my attached solution and let me know if you have any questions.