crosstable/pivot my CY column

[124rooski]

I have a table that I need to pivot so when creating visualizations in Qlik, I can use the dimensions properly but am having a hard time setting this up in my script. Ideally, I'd like to create an additional column name "Sales" and instead that will show each sale for a given year and which category it maps to.  I'm trying to have all my calendar years grouped as "Year".

crosstable(CalendarYear, Sales,  4)

[Sheet1-1]:
LOAD
[STORE],
[CITY],
[STATE],
[CATEGORY],
[CY2017],
[CY2018],
[CY2019],
[CY2020],
FROM [lib://AttachedFiles/Crosstable_Calendar_year.xlsx]
(ooxml, embedded labels, table is Sheet1);

This script only leaves me with 3 fields, I ultimately would like to have all my sales by category and have my years pivoted.  Any feedback is appreciated. Thanks all.

[dplr-rn · Accepted solution]

the script you shared has an errant ',' after cy2020

so i am assuming is not the full script snippet.

below script works. i tried with a dummy excel

CrossTable(CalYear, Saless, 4)
LOAD STORE,
CITY,
STATE,
CATEGORY,
CY2017,
CY2018,
CY2019,
CY2020
FROM
[..\Downloads\text.xlsx]
(ooxml, embedded labels, table is Sheet1);

[124rooski]

Thank you. And yes, my code did get snipped off there but this worked perfectly!