Cross Table

[#1]

Hi All,

I'm trying to do a cross table in Qlik but it's not producing the desired results. I want something like this but my QVW isn't working out for

some odd reason. If anyone can provide insights please do.

Thanks,

[#2]

Forgot to attach the QVW.

[Kushal_Chawda]

try this

Cohort:

CrossTable (Months, DATA,5)

LOAD [Customer Internal ID],

     Customer,

       Average,

     [% of Total],

     Category,

     [2015 09],

     [2015 10],

     [2015 11],

     [2015 12],

     [2016 01],

     [2016 02]

FROM

[Churn Summary - 2015 03 30 Latest.xlsx]

(ooxml, embedded labels, table is [MRR by Customer (2)]);

CohortTable:

NoConcatenate

LOAD [Customer Internal ID],

     Month(Date#(Months, 'MMM')) as Months,

     DATA

Resident Cohort;

DROP Table Cohort;

[#4]

No the months column still empty. The problem is [2015 09] to [2016 02] have numbers stored within them I can't use them as dimensions and then expressions for both Cross Table is the only tactic I can think of. Months as Dimensions and their sum as expression.

[Kushal_Chawda]

Can you share the excel file?

[#6]

Please find attached the document.

[#7]

Can someone please provide any insights on this issue?

[Kushal_Chawda · Accepted solution]

Data:
CrossTable(Month,Data,3)
LOAD [Customer Internal ID],
Average,
[% of Total],
[2015 09],
[2015 10],
[2015 11],
[2015 12],
[2016 01],
[2016 02]
FROM
[STK.xlsx]
(ooxml, embedded labels, header is 2 lines, table is Sheet1);

New:
LOAD monthname(date#(Month,'YYYY MM')) as MonthYear,
[Customer Internal ID],
Data
Resident Data
where [Customer Internal ID]<>'#N/A' and not IsNull([Customer Internal ID]);

DROP Table Data;

[tamilarasu]

Hi Aaqureshi,

Here is the dynamic code which will load all the columns.

Temp:
CrossTable([YearMonth], Data)
LOAD * FROM
STK.xlsx
(ooxml, embedded labels, header is 2 lines, table is Sheet1);

NoConcatenate
LOAD [Customer Internal ID],
Left(YearMonth,4) as Year,
Month(Date#(Right(YearMonth,2),'MM')) as Month,
YearMonth,
Data
Resident Temp where Not Match(YearMonth,'Average','% of Total') and [Customer Internal ID] <>'#N/A';
Drop Table Temp;

[#10]

Thanks guys for all your help.