Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
ionuttatu
New Contributor II

Formula Help - That shows me the months available in format YYYYMM

Hi,

I would like to make a selection that lets me select a YYYYMM between 201701 and 201812 (Capture.PNG)

In my data I have a column for YEARMONTH starting with 201601 to 201812

This is the initial formula but it only shows me 201601 to 201612 and 201801 to 201812

=Concat({<YearMonth=-{'$(Year)'}>}distinct YearMonth,',')

$(Year) =year(today())-1&'*'

Can you help me understand the formula?

Tags (1)
7 Replies

Re: Formula Help - That shows me the months available in format YYYYMM

This will return like

YearMonth

201601

201602

201705

201801

201811

201811

Concat(DISTINCT YearMonth, ',') -- It ignores the duplicate values

201601, 201602, 201705, 201801, 201811

Your expression work as Concat({<YearMonth -= {'$(Year)'}>} DISTINCT YearMonth, ',')

So, The variable year returns $(Year) =year(today())-1&'*' ------ It displays last year data which wildcard function like 2017* -- Wildcard data loading (*.blah......)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: Formula Help - That shows me the months available in format YYYYMM

Not sure I understand completely, would you be able to share a sample and show your issue?

MVP
MVP

Re: Formula Help - That shows me the months available in format YYYYMM

As Anil pointed out, the variable is a match to all months in the current year

The expression is excluding everything in that match, so, 2016 and 2018 will be displayed and 2017 will be excluded.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
ionuttatu
New Contributor II

Re: Formula Help - That shows me the months available in format YYYYMM

YearMonth -= {'$(Year)'}


so the '-' is excluding the values that are the same with last year

I think I get it now.

The formula is ok. But I need to change the $YEAR formula....annnnnnd.... now it works!

Thank you guys for the help.

Re: Formula Help - That shows me the months available in format YYYYMM

What you mean? to where you want to write?

The formula is ok. But I need to change the $YEAR formula....annnnnnd.... now it works!

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: Formula Help - That shows me the months available in format YYYYMM

Since you got what you were looking for, please close the thread by marking correct and helpful responses.

Best,

Sunny

ionuttatu
New Contributor II

Re: Formula Help - That shows me the months available in format YYYYMM

It's ok I understood the condition formula 

=Concat({<YearMonth=-{'$(Year)'}>}distinct YearMonth,',')


and the variable formula


$(Year) =year(today())-1&'*'


And I changed it to show me the info from 201701  to 201812