Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
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
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
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
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.
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
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)));
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..
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