Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I want a sequence as below with comma separated value as variable. This indicates current calendar year and its month
201401,201402,201403,201404,201405,201406,201407,201408,201409,201410,201411,201412 .
The above value should be stored in a variable. Can I do this using a for loop?
no need.. try using below statement in your variable..
assuming you have a field YEARMONTH in your datamodel..
=Concat(Distinct YEARMONTH, ',')
Hi Pavan,
Try like this
=Concat(Year(Today()) & Num(ValueLoop(1, 12, 1), '00'), ',')
Regards,
Jagan.
Hi,
If you have dimension already then use
=Concat({<MonthYearDimensionName=, YearDimensionName={$(=Year(Today()))}>}MonthYearDimensionName, ',')
Note : MonthYearDimensionName and YearDimensionName with your actual field names.
Regards,
Jagan.
Thanks Jagan. This really helped a lot. I would like to extend this to generate sequence from last year to current month. i.e from 201301,201302,.... 201401,201402..201409. Can you suggest better idea . Should I use a concat again?
Hi,
Try like this
In script:
LET vStartDate = Num(YearStart(Today(), -1));
LET vEndDate = Num((Today()));
Data:
LOAD
RowNum,
Date($(vStartDate) + RowNum -1, 'YYYYMM') AS Date;
LOAD
RangeSum(Peek('RowNum'), 1) AS RowNum
AutoGenerate vEndDate - vStartDate + 1;
Temp:
LOAD
Concat(DISTINCT Date, ',') AS ConcatMonths
RESIDENT Data;
LET vConcatMonths = FieldValue('ConcatMonths', 1);
DROP TABLES Data, Temp;
Now the variable vConcatMonths has the concatenated months.
Regards,
Jagan.