How to create these pivot table

philgood34 — Thu, 05 Oct 2017 20:08:56 GMT

I'm trying to create a simple pivot table using these tab datas

with a side filter for sectors

Maybe it depends on my data model, so i join my sample app.

If you could show me the way directly in my app. it would very helpful

thank's in advance

Philippe

Re: How to create these pivot table

philgood34 — Fri, 06 Oct 2017 06:21:30 GMT

Of course i tried this

But, as shown, i don't want Year in column but in a filter which would make it possible to vary the measurements in the columns PREVIOUS / CURRENT & TARGET ...

I show you a an excerpt from my script where you can see my data model about previous, current and goal :

[NCL_SIMP$]:

LOAD [NCL] AS [NCL-VMS_NCL],

[NCL_LIB]

FROM [lib://TB_CROISE/NCL_SIMP_BD.xls]

(biff, embedded labels, table is NCL_SIMP$);

[IMPORT$]:

LOAD

[IMPORT_NCL] AS [NCL-VMS_NCL],

[CIB_MBTVTE]

FROM [lib://TB_CROISE/CIBLES_2017.xls]

(biff, embedded labels, table is IMPORT$)

WHERE EXISTS ([NCL-VMS_NCL], [IMPORT_NCL]);

[SALES$]:

LOAD Date(MakeDate([VMS_ANN], [VMS_MOI]), 'MM_YYYY') as MonthYear,

[VMS_ANN],

[VMS_MOI],

[VMS_NCL] AS [NCL-VMS_NCL],

[VMS_CAHT],

[VMS_MBTCAL]

FROM [lib://TB_CROISE/SALES.xls]

(biff, embedded labels, table is SALES$)

WHERE EXISTS ([NCL-VMS_NCL], [VMS_NCL]);

[SALES_T$]:

LOAD Date(MakeDate([VMP_ANN], [VMP_MOI]), 'MM_YYYY') as MonthYear,

[VMP_ANN],

[VMP_MOI],

[VMP_NCL] AS [NCL-VMS_NCL],

[VMP_CAHT],

[VMP_MBTCAL]

FROM [lib://TB_CROISE/SALES_TARGET.xls]

(biff, embedded labels, table is SALES_T$)

WHERE EXISTS ([NCL-VMS_NCL], [VMP_NCL]);

AsOfTable:

LOAD MonthYear as AsOfMonthYear,

MonthYear,

'CY' as Flag

Resident [SALES$];

Concatenate (AsOfTable)

LOAD MonthYear as AsOfMonthYear,

Date(AddYears(MonthYear, -1), 'MM_YYYY') as MonthYear,

'PY' as Flag

Resident [SALES$];

Concatenate (AsOfTable)

LOAD MonthYear as AsOfMonthYear,

MonthYear,

'Goal' as Flag

Resident [SALES_T$];

Hope it’s would be helpful

Regards

Philippe

topic Re: How to create these pivot table in QlikView

How to create these pivot table

Re: How to create these pivot table