Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Thanks. Perfect solution:)
But i need to calculate count in expr <current YearMonth-YearMonth-2>
My idea is how to set YearMonth_Test filter in expr by field YearMonth with ignoring YearMonth_Test dimension