Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
1. Can you insert a formula instead of a hard-coded value to return a value in an inline table?
2. If so, what is the proper syntax?
Ex:
CloseYear:
Load * Inline [
CloseYear, CloseYearCat
2014, 2014
2015, 2015
2014, Delta
2015, Delta
2015, Goal
];
I want to enter a function for CloseYear, something like Year(Today()) and get it to return "2015" instead of having to actually type in "2015" as above.
Is this possible?
Thanks!
You could instead of having a * after load do the function you mentioned - something like this:
CloseYear:
Load
Pick( Match( CloseYear , 'ThisYear' , 'LastYear' ) , Year( Today() ) , Year( AddMonths( Today() , -12 ))) AS CloseYear,
Pick( Match( CloseYear , 'ThisYear' , 'LastYear' ) , Year( Today() ) , Year( AddMonths( Today() , -12 ))) AS CloseYearCat
Inline [
CloseYear, CloseYearCat
PreviousYear, PreviousYear
ThisYear, ThisYear
PreviousYear, Delta
ThisYear, Delta
ThisYear, Goal
];
Or maybe like this:
SET YearList = ['TY' , 'LY' , 'Y2', 'Y3', 'Y4' ];
LOAD
Year( Today() ) - Match( Y1 , $(YearList) ) + 1 AS Y1,
Year( Today() ) - Match( Y2 , $(YearList) ) + 1 AS Y2
INLINE [
Y1, Y2
TY, LY
TY, TY
TY, Y4
LY, Y3
];
Something like this
Load *, Evaluate(CloseYear) as CloseYearNew,
Evaluate(CloseMonth) as CloseMonthNew
Inline [
CloseYear,CloseMonth
year(today()),month(today())
];
Thanks!
Thanks!
Thank you Kushal!! This was exactly what I was looking for!!