Why Is the Pivot Tile not Working as I Expected

Roman_K

I am trying to use the Pivot tile to accomplish the following (in my attached dataset), but am getting no results under the column I am creating (Weekend_Date_Pivot).

Weekend Date should now be columns with Demand_Qty as the metric under each Weekend Date

Inventory - Example File.xlsx

MichelleH

@Roman_K In the Pivot tile, you will need to specify a value of WEEK_END_DATE in step 4 for every column you want displayed in the final dataset.

I don't recommend pivoting by date in Magic ETL for a few reasons:

1. It is cumbersome to add every possible date column when creating the dataflow
2. It requires constant upkeep as your data updates, you will need to manually add new date columns to the ETL as new data populates
3. The output dataset no longer contains a usable date field, so you lose the ability to filter the dataset by date
4. Similar to #2, as the ETL continues to add columns, any cards you build with the output dataset will need constant upkeep to display the new columns

Instead, I recommend skipping the Pivot tile in the ETL and displaying the output data in a Pivot Table card. This will preserve the native filter functionality of the WEEK_END_DATE field and require minimal maintenance.

ColemenWilson

There isn't, but you could reduce the available data on the pivot table card using filters or pre-aggregate the data in an ETL.

ColemenWilson

Could you share screenshots of your ETL? The pivot tile details.

Roman_K

Hi. Thank you for providing direction, it is much appreciated. Here are the details of my pivot tile, but the column I'm creating (Demand_Qty by Week_End_Date) is still empty for all rows.

Roman_K

Is it not working properly perhaps because an ItemNumber can have multiple SKUs?

MichelleH

@Roman_K In the Pivot tile, you will need to specify a value of WEEK_END_DATE in step 4 for every column you want displayed in the final dataset.

I don't recommend pivoting by date in Magic ETL for a few reasons:

1. It is cumbersome to add every possible date column when creating the dataflow
2. It requires constant upkeep as your data updates, you will need to manually add new date columns to the ETL as new data populates
3. The output dataset no longer contains a usable date field, so you lose the ability to filter the dataset by date
4. Similar to #2, as the ETL continues to add columns, any cards you build with the output dataset will need constant upkeep to display the new columns

Instead, I recommend skipping the Pivot tile in the ETL and displaying the output data in a Pivot Table card. This will preserve the native filter functionality of the WEEK_END_DATE field and require minimal maintenance.

Roman_K

Thank you for your suggestion, I tried it, but am getting the message that it's too much data (over 100k rows). Is there a way to accommodate larger data sets in a Pivot Table card?

ColemenWilson

There isn't, but you could reduce the available data on the pivot table card using filters or pre-aggregate the data in an ETL.