working with a date in expression

[pgonin]

Hello guys!

I have a column in pivot table with following expression, which shows only last 3 months (current maximum month is '2019-09'):

=if(AsOfPeriod>='$(=vMaxAsOfPeriod3)',AsOfPeriod).

Unfortunately, it shows me nothing! While if I change the expression to =if(AsOfPeriod>='2019-07',AsOfPeriod) it shows me the desired columns: '2019-07', '2019-08', '2019-09'.

vMaxAsOfPeriod3 is a variable which equals to date(AddMonths(max({1}AsOfPeriod),-2)) (I checked in text object, this variable is being calculated correctly).

I believe the answer is easy, but still what am I doing wrong?

[Stoyan_Terziev · Accepted solution]

Hi Buddy,

There could be several problems related to this.

Check the code below which will help you identify a working solution.

SUB ForumMaxDate




	Data:	
// Entry Dataset
	LOAD
		PRODUCT,
		AMOUNT,
		AsOfPeriod_Cool,
		AsOfPeriod_UnCool,
		DATE,
		Num(AsOfPeriod_Cool) as Flag_Cool,
//this one should be used for calculation if you want to get reliable data from the start of the month

		Num(AsOfPeriod_UnCool) as Flag_UnCool
	;
	LOAD
		PRODUCT,
		AMOUNT,
	 	Date(Date#(DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') 	as DATE,	
//Here we make sure the dateformat is clearly identified

	 	Date(Date#(DATE, 'DD/MM/YYYY'), 'YYYY-MM') 		as AsOfPeriod_UnCool,
//This may be confusing since The date will be presented with the format YYYY-MM but the numeric value it's equivalent to will not change

	 	Date(MonthStart(Date#(DATE, 'DD/MM/YYYY')), 'YYYY-MM') 		as AsOfPeriod_Cool
//This floors the numeric value to the one of the starting date of the month
	;
	LOAD * INLINE [
	    PRODUCT, DATE, AMOUNT
	    A, 01/02/2019, 300
	    A, 05/02/2019, 140
	    A, 01/03/2019, 200
	    A, 08/03/2019, 500
	    A, 05/04/2019, 140
	    A, 21/04/2019, 200
	    A, 08/05/2019, 500
	    A, 15/06/2019, 140
	    A, 18/07/2019, 200
	    A, 07/08/2019, 500
	    A, 25/08/2019, 140
	    A, 01/09/2019, 200
	    A, 20/09/2019, 500
	];



END SUB

And then your variable:

=Date(MonthStart(AddMonths(max({1} AsOfPeriod_Cool),-2)),'YYYY-MM')

[pgonin]

Thank you! Now it works 🙂