This content has been marked as final.
Show 2 replies
-
1. Re: Complex Pivot in Prep
Miguel Escobar Jun 5, 2018 3:52 PM (in response to Tim Zalk)You could unpivot the dataset, but then there's no way to pivot it again using the "gross sales" and "units".
Here's an article which explains a way to accomplish what you want. It's still quite complicated and not that flexible, but it gets the job done.
For the part on transforming Text based month names into dates, you could create a really well done formula (wish that I could help with that!), or you could have another table that would act as a translation table with values like:
January -- 2018-01-01
February --- 2018-02-02
March --- 2018-03-01
and do a simple merge/union operation for that. Like it was some sort of VLOOKUP
-
2. Re: Complex Pivot in Prep
Tim Zalk Jun 5, 2018 4:49 PM (in response to Miguel Escobar)That's it! Once I split the original sheet into two concurrent pivots, it was easy! Thanks for the article!