topic Re: hold all months less than current month in variable in QlikView

hold all months less than current month in variable

Anonymous — Thu, 10 Nov 2016 15:34:49 GMT

Hi All, How do I define a variable which holds all months(separated by comma) less than current month in a variable?

Cheers!

Re: hold all months less than current month in variable

johnw — Fri, 11 Nov 2016 00:56:02 GMT

Assuming your months are defined as dates (like the first of the month, formatted to not show the day), then I believe this:

=concat({<Month={"=Month<monthstart(today())"}>} distinct Month,',',Month)

Re: hold all months less than current month in variable

Anonymous — Mon, 14 Nov 2016 00:19:53 GMT

Thanks John!

I tried this but my variable didn't return anything.. I think I am doing something wrong,

Here is my requirement:

If current month is Sept, my variable should return - Jan, Feb, Mar, Apr, May, Jun, Jul, Aug(i.e Months in MMM format separated by commas).

Thanks!

Re: hold all months less than current month in variable

johnw — Mon, 14 Nov 2016 16:43:54 GMT

How are your months defined?

date(monthstart(Date),'MMM') as Month
month(Date) as Month
'Sep' as Month
other?

Re: hold all months less than current month in variable

Anonymous — Mon, 14 Nov 2016 23:31:08 GMT

I read the current date from Today() function.

So I use Month(Today()) to get current month.

I want to use a variable which will store all MTD Months separated by commas(eg: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug).

Please let me know if you need anything further.

Re: hold all months less than current month in variable

johnw — Mon, 14 Nov 2016 23:44:37 GMT

OK, if you're using month(today()) to get the current month, then I'm guessing you're using month(Date) as Month instead of monthstart(Date). In that case, just replace my monthstart() with month(). Hopefully this does the trick?

=concat({<Month={"=Month<month(today())"}>} distinct Month,', ',Month)