topic Re: Link two types YearMont in expr in QlikView

Link two types YearMont in expr

Anonymous — Tue, 24 May 2016 07:11:10 GMT

Hi everyone.

Colleagues, in my test model i want to connect two types of YearMonth period:

First: YearMonth is loaded from Sheet1

Second: YearMonth_Test is loaded from Sheet2.

Also i have link table, where every value from YearMonth Test have three values. (YearMonth_1,YearMonth_2,YearMonth_3)

My task to make such link: Every YearMonth_test connect with current YearMonth value and two previous.

in expression i try to calculate count of Delivery Points using variables in filter for field YearMonth (by this filed i need to calc this expresion):

count({<YearMonth={$(v1),$(v2),$(v3)}>} DISTINCT DeliveryPoint).

But in result i get wrong values:

YearMonth_Test	Result
201503	5
201504	5
201505	5
201506	5

My necessary result:

YearMonth_Test	Result	Comments
201503	32	count for YearMonth 201503,201502,201501
201504	32	count for YearMonth 201504,201503,201502
201505	34	count for YearMonth 201505,201504,201503
201506	38	count for YearMonth 201506,201505,201504

Model and source file in attachment.

For any idea, will be glad.

Thanks.

Re: Link two types YearMont in expr

sunny_talwar — Tue, 24 May 2016 11:12:17 GMT

This?

Script:

Main:

LOAD Date(Date#(YearMonth, 'YYYYMM'), 'YYYYMM') as YearMonth,

DeliveryPoint

FROM

[Test (2).xlsx]

(ooxml, embedded labels, table is Sheet1);

Link:

LOAD DISTINCT YearMonth as Report_YearMonth,

AddMonths(YearMonth, -(IterNo() - 1)) as YearMonth

Resident Main

While IterNo() <= 3;

Re: Link two types YearMont in expr

Anonymous — Thu, 26 May 2016 07:56:13 GMT

Thanks. Perfect solution:)

But i need to calculate count in expr <current YearMonth-YearMonth-2>

Re: Link two types YearMont in expr

Anonymous — Thu, 26 May 2016 08:03:51 GMT

My idea is how to set YearMonth_Test filter in expr by field YearMonth with ignoring YearMonth_Test dimension