Months as variable

[sculptorlv]

Hello!

How can I create a variable like Month_Variable, which will containt all month values for last 10 years.

For instance, now is 20.06.2018. Thus, I need the following values for the variable Month_Variable:

          06.2018

          05.2018

          04.2018

          etc..

[YoussefBelloum]

Hi,

here is one way to do it:

Let varMinDate = num(YearStart(Today()));

Let varMaxDate = Num(Date(today()));

Date:

LOAD num(Month($(varMinDate)+IterNo()-1),00)&'.'&Year($(varMinDate)+IterNo()-1) AS Date

AUTOGENERATE (1)

WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

resident:

LOAD Concat(Distinct Date,',') as concat_date Resident Date;

let vMonth_variable = peek('concat_date',0,'resident');

==> just call the "vMonth_variable" on a textbox for example to see the exptected output

[jyothish8807]

Hi Ruslans,

A variable can only store one value at a time. So you cannot have multiple months as rows.

So as mentioned above by youssefbelloum‌ you have to do a concat(Date) and use them in your expressions.

Br,

KC

[sculptorlv]

Yes, I already understood my mistake. I was wrong with the temr VARIABLE.

I need just a data base filed, which will contains these values. Then I will combine it with other fields.

[jyothish8807 · Accepted solution]

perfect

Then this section would be enough for you:

Let varMinDate = num(YearStart(Today()));

Let varMaxDate = Num(Date(today()));

Date:

LOAD num(Month($(varMinDate)+IterNo()-1),00)&'.'&Year($(varMinDate)+IterNo()-1) AS Date

AUTOGENERATE (1)

WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

Br,

KC

[YoussefBelloum]

EDITED

so you need this:

Let varMinDate = num(YearStart(Today()));

Let varMaxDate = Num(Date(today()));

Date:

LOAD num(Month($(varMinDate)+IterNo()-1),00)&'.'&Year($(varMinDate)+IterNo()-1) AS Date

AUTOGENERATE (1)

WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

NOCONCATENATE //or just use the Date table without loading below code

the_table_you_need:

LOAD Date Resident Date;

Drop table Date;

PS: to get the values for the last 10 years, just change the varMinDate variable to this:

Let varMinDate = num(YearStart(addyears(Today(),-10)));

[sculptorlv]

Thank you a lot! This thing works!

Can you please also advise, how I can combine it with my another table, which has its own dates?

Date            Value

14.05.2018  15

29.07.2018   40

I need this result:

...

14.05.2018          15    05.2018

...                         ...     06.2018

29.07.2018          40     07.2018

---                          ---     08.2018

etc.

Or to be more precise ... I need to get total sums for each month..

[jyothish8807]

Try like this:

t1:

Load

Date,

Date(Date,'MM.YYYY') as NewDate

Value from Table1;

join (t1)

load

Date as NewDate

from the newly created table;

Br,

KC