Sequence generation

[pavanpatha]

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?

[phaneendra_kunc]

no need.. try using below statement in your variable..

assuming you have a field YEARMONTH in your datamodel..

=Concat(Distinct YEARMONTH, ',')

[jagan]

Hi Pavan,

Try like this

=Concat(Year(Today()) &  Num(ValueLoop(1, 12, 1), '00'), ',')

Regards,

Jagan.

[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.

[pavanpatha]

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?

[jagan]

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.