Set Analysis: Date when Variable is > 0

[50ShadesOfSalty]

Hi experts,

Lately i've been trying to develop two variables based on the following conditions:

- 1st variable: It should return the max Date that is available in the Data Model. No big question in here, the following expression is working just fine:

Var 1:Max({<Field1={'*ValueNeeded*'}>}  YearMonthField). Example of output: 201408

- 2nd Variable: This one should return the min Date of 12 months. But here is the twist: If there are sales on the previous 12 months, that date should be retrieved. However, if the previous 12 months doesn't have any sales, it should return that last month between the max selected and the 12 month that has sales. From what i did in the expression, i got this:

Var 2: Date(Min({< [FieldY]-={'$(=null())'}, YearMonthField={">=$(Previous12YearMonthField)<=$(CurrentYearMonthField)"}>} [Date]),'YYYYMM')

Taking into account, by this selections:

In the selection 1, the Variable1 should be: 201503; the Variable2 should be: 201403. (As long as all the previous 12 months have sales, it will return 201403).

In the selection 2, the Variable1 should be: 201408; the Variable2 should be: 201401. (As long as only 6 out of 12 previous months have sales, it should return 201408).

PS: The sales are calculated via expression, for example $(VarSales).

I really need help on this situation.

Thank you for all of your help,

Regards.

[felipedl]

Hi,

I've assumed your data to make the expression like so:

load
*,
Num(date#(left(Date,4)&'-'&right(Date,2),'YYYY-MM')) as Date_Num;
load * Inline
[
Date,Sales
201311,0
201312,0
201401,0
201402,23
201403,560
201404,1
201405,263
201406,14
201407,50
201408,67
201409,0
201410,24
201411,29
201412,21
201501,23
201502,698
201503,20
201504,15
]

By doing that, i have the following:

With the expression:

=if(count({<Date,Date_Num={">=$(=Num(AddMonths(max(Date_Num),-11)))<=$(=max(Date_Num))"},Sales={">0"}>}Date)<12,max({<Date,Date_Num={">=$(=Num(AddMonths(max(Date_Num),-11)))<=$(=max(Date_Num))"},Sales={"0"}>}Date),min({<Date,Date_Num={">=$(=Num(AddMonths(max(Date_Num),-11)))<=$(=max(Date_Num))"},Sales={">0"}>}Date))

You can get the latest max date that contains no Sales

I've added another "zero" valued sales month, giving

That gives the period that you want for the months that have sales for the past n months.

For my expression, i've used -11 (for 12 months) so you only need to change it to -5 (for 6 months)

Also attached is the app i used.

Hope it helps.