Suppose you have this in an external file (it can be an inline table too), with the structure you mentioned:
Store# StoreName StoreLocation num_of_employee Total Sales Month Year
123 Chales California 55 100 01 2014
123 Chales California 75 200 02 2014
123 Chales California 100 500 04 2014
123 Chales California 44 600 06 2014
123 Chales California 22 700 08 2014
200 Delta Kansas 55 1000 01 2014
200 Delta Kansas 55 1200 02 2014
200 Delta Kansas 55 100 04 2014
200 Delta Kansas 55 1400 10 2014
200 Delta Kansas 55 2000 12 2014
when you load it to QlikView just change the order of the loaded fields (ultimately this doesn't not affect the structure of data), like this:
CrossTable(Description, Data, 4)
LOAD Store#,
Month,
Year,
Year & '-' & Month as YearMonth,
TotalSales,
StoreName,
StoreLocation,
Num_of_employee
FROM
crosstable.xlsx
(ooxml, embedded labels, table is Sheet1);
crosstable function takes 2 or 3 parameters: the names of the new fields that you will create (in this case description and data, this names can be whatever) and the number of columns that will NOT be transposed. That’s why there’s a 4 in parameters because Store#, Month, Year and the newly created field YearMonth will not be transposed.
After that, you create an inline table with numeric references, that will be used in the visualization table in order to create a case scenario:
LOAD * INLINE [
DescriptionID, Description
1, Num_of_employee
2, StoreLocation
3, StoreName
4, TotalSales
];
After reloading script, create a pivot table with Store#, Description (This is the field generated by the crosstable function) and YearMonth as dimensions. In the expression put this:
pick(DescriptionID,
only({$<Description={'Num_of_employee'}>}Data),
only({$<Description={'StoreLocation'}>}Data),
only({$<Description={'StoreName'}>}Data),
only({$<Description={'TotalSales'}>}Data)
)
This will create a case scenario. It means that the calculated expression will be different for every description available. In this example you have 4 possible descriptions, so you have 4 possible expressions. It is important to mention that in expression wizard you have to put the expressions in the same order that is in your inline table where descriptionID is generated (otherwise you will need to define a custom sort order).
Finally you need to put yearmonth as a vertical dimension like in the example I sent you. Like I said, there are many ways to get to the same result, just try what better fits to you,
regards
