topic Re: Date issue in QlikView

Date issue

guillaume_gorli — Mon, 15 May 2017 13:32:53 GMT

Hi All,

Please see below the issue i am facing :

I have a test database with :

Date

Part Number

Cost

Then i have a second database showing a cost saving target per month :

What i am trying to do is to sum up in a chart a cost including the target savings per part number.

The output should look like :

Although what i got is :

Thanks for your help

Guillaume

Re: Date issue

vinieme12 — Mon, 15 May 2017 13:42:27 GMT

try joining the data with a new field

LOAD Date,

[Part Number],

Cost,

MonthEnd(Date) as SavingsDate

FROM

(ooxml, embedded labels, table is Sales);

/// SAVINGS ///

LOAD Date as SavingsDate,

Savings

FROM

(ooxml, embedded labels, table is Savings);

Re: Date issue

guillaume_gorli — Mon, 15 May 2017 15:10:34 GMT

Hi Vineeth,

It does not seem to work this way :

Re: Date issue

vhespinog — Mon, 15 May 2017 15:39:08 GMT

Hi.

Sales:

LOAD Year(Date) & Month(Date) as %Date,

Date,

[Part Number],

Cost

FROM

[..\Downloads\TEST QLIK.xlsx]

(ooxml, embedded labels, table is Sales);

Savings:

LOAD

Year(Date) & Month(Date) as %Date,

Date as SavingsDate,

Savings

FROM

[..\Downloads\TEST QLIK.xlsx]

(ooxml, embedded labels, table is Savings);

Re: Date issue

vinieme12 — Tue, 16 May 2017 07:24:48 GMT

sorry, slight change below

LOAD Date,

[Part Number],

Cost,

MonthEnd(Date) as SavingsDate

FROM

(ooxml, embedded labels, table is Sales);

/// SAVINGS ///

LOAD MonthEnd(Date) as SavingsDate,

Savings

FROM

(ooxml, embedded labels, table is Savings);

Re: Date issue

vinieme12 — Tue, 16 May 2017 07:52:31 GMT

or you can directly use MonthName()

Re: Date issue

anushree1 — Tue, 16 May 2017 07:54:40 GMT

Hi,

Please check if this at serves the requirement

Re: Date issue

prieper — Tue, 16 May 2017 09:56:36 GMT

Think that you should use INTERVALMATCH. Would suggest to enter a date with 0% Savings for 1/1/2017 (begin of the Sales-activities), but anyhow:

/// COST ///

Sales:

LOAD Date,

[Part Number],

Cost

FROM

(ooxml, embedded labels, table is Sales);

/// SAVINGS ///

Savings:

LOAD Date,

Savings

FROM

(ooxml, embedded labels, table is Savings);

Savings_FmTo:

LOAD

Date AS Date_Fm,

PREVIOUS(Date) AS Date_To,

Savings

RESIDENT

Savings

ORDER BY

Date DESC;

DROP TABLE Savings;

INTERVALMATCH (Date) LOAD Date_Fm, Date_To RESIDENT Savings_FmTo;

HTH Pete

Re: Date issue

guillaume_gorli — Wed, 17 May 2017 16:09:23 GMT

Thanks a lot