topic Re: Months as variable in QlikView

Months as variable

sculptorlv — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Months as variable

YoussefBelloum — Wed, 20 Jun 2018 08:39:48 GMT

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

Re: Months as variable

jyothish8807 — Wed, 20 Jun 2018 09:08:34 GMT

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,

Re: Months as variable

sculptorlv — Wed, 20 Jun 2018 09:13:41 GMT

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.

Re: Months as variable

jyothish8807 — Wed, 20 Jun 2018 09:17:30 GMT

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,

Re: Months as variable

YoussefBelloum — Wed, 20 Jun 2018 09:18:38 GMT

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)));

Re: Months as variable

sculptorlv — Wed, 20 Jun 2018 09:25:47 GMT

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

Re: Months as variable

jyothish8807 — Wed, 20 Jun 2018 09:41:29 GMT

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,