topic Re: Cross table in QlikView

Cross table

krishna20 — Thu, 12 Nov 2015 13:41:45 GMT

Hi folks,

Having a problem in creating cross table. I'm having product sales as month wise. I need to calculate it ,as overall in single column.I need to create a month field as well as year field in the cross table.Please find the attached sample excel data.

Re: Cross table

Anonymous — Thu, 12 Nov 2015 13:45:51 GMT

Try this at script level:

CrossTable(Month, Data_Value, 3)

LOAD Freezer_ID,

Category_ID,

Freezer_Name,

JAN,

FEB,

MAR,

APR,

MAY,

JUNE,

JULY,

AUG,

SEP,

OCT,

NOV,

[TOTAL SALES]

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Cross table

sunny_talwar — Thu, 12 Nov 2015 13:46:54 GMT

Not sure where the year information is, but assuming everything is 2015, may be this:

Table:

CrossTable(Month, Value, 4)

LOAD Freezer_ID,

Category_ID,

Freezer_Name,

[TOTAL SALES],

JAN,

FEB,

MAR,

APR,

MAY,

JUNE,

JULY,

AUG,

SEP,

OCT,

NOV

FROM

Test_Help.xlsx

(ooxml, embedded labels, table is Sheet1);

NewTable:

LOAD Freezer_ID,

Category_ID,

Freezer_Name,

[TOTAL SALES],

MonthName(MakeDate(2015, Month(Alt(Date#(Month, 'MMM'), Date#(Month, 'MMMM'))), 1)) as MonthYear,

Value

Resident Table;

DROP Table Table;

Re: Cross table

Anonymous — Thu, 12 Nov 2015 13:46:56 GMT

PFA...Hope this will help

Re: Cross table

Chanty4u — Thu, 12 Nov 2015 13:49:55 GMT

PFa

Re: Cross table

krishna20 — Thu, 12 Nov 2015 14:19:40 GMT

Hi Sunny,

Thank you for your prompt reply. Absolutely right solution.

But, how can i insert years in excel or qlikview app??

I mean how i show current year and previous year sales. Please show light on this issue.

Re: Cross table

sunny_talwar — Thu, 12 Nov 2015 14:25:15 GMT

May be you can add it as a column?

If this is acceptable, I can send you an updated script

Re: Cross table

krishna20 — Thu, 12 Nov 2015 14:28:21 GMT

Yes Sunny . It's acceptable.Can you please share updated script..

Re: Cross table

sunny_talwar — Thu, 12 Nov 2015 14:31:36 GMT

Try this:

Table:

CrossTable(Month, Value, 5)

LOAD Freezer_ID,

Category_ID,

Freezer_Name,

Year,

[TOTAL SALES],

JAN,

FEB,

MAR,

APR,

MAY,

JUNE,

JULY,

AUG,

SEP,

OCT,

NOV

FROM

Test_Help.xlsx

(ooxml, embedded labels, table is Sheet1);

NewTable:

LOAD Freezer_ID,

Category_ID,

Freezer_Name,

[TOTAL SALES],

MonthName(MakeDate(Year, Month(Alt(Date#(Month, 'MMM'), Date#(Month, 'MMMM'))), 1)) as MonthYear,

Value

Resident Table;

DROP Table Table;

Re: Cross table

krishna20 — Thu, 12 Nov 2015 14:57:12 GMT

Thank You Very Much Sunny..