Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ronman10
New Contributor III

Expression to show YTM data

Hi Guys,

I am trying to create a YTM graph using current and last year data, attached is a sample qvf file. In the first sheet of the attached qvf, the graph is on YTM basis. I have done this by defining 2 columns with data restricted on a YTM (Till June) basis. However, I do not want to create these additional columns as I already have a single column where data from Jan 2017 to June 2018 is available. Now, for my comparsion between 2017 YTM and 2018 YTM, I can easily use an expression for YTM 2018 as only YTM data is available for 2018. However, for 2017, I have the full year data. I want to be able to define YTM 2017 using an expression in such a way that I am able to apply month filters to the YTM graph. For example, if I select Jan, Feb and March as filter, both 2017 YTM and 2018 YTM bars should show 3 months cumulative data and so on. Hope my explanation is clear. Please help me with this.

An expression which helps me select data suing the maximum function, upto June is what I am looking for. Please advise.

Regards,

Rony

1 Solution

Accepted Solutions
MVP
MVP

Re: Expression to show YTM data

For month filter to applied in addition to set analysis on the month field... you can try this...

Sum({$<Year = {"2017"}, [Month Short] *= {'Jan','Feb','Mar','Apr','May','June'}>} [Value])

7 Replies
MVP
MVP

Re: Expression to show YTM data

But if you select Jan, Feb, Mar... it should already be showing your three months for the two years... not sure I understand the issue?

ronman10
New Contributor III

Re: Expression to show YTM data

Hi Sunny,

It does not. Could you write an expression where 2017 data is selected till june. Year, month and data defined in 3 individual columns. This is the formula is used earlier:

sum({$<Year={"2017"},[Month Short]={'Jan','Feb','Mar','Apr','May','June'}>}[Value])

However, the month filter is not getting applied as the months have already been defined. Hope this is clearer.

Regards,

Rony

MVP
MVP

Re: Expression to show YTM data

For month filter to applied in addition to set analysis on the month field... you can try this...

Sum({$<Year = {"2017"}, [Month Short] *= {'Jan','Feb','Mar','Apr','May','June'}>} [Value])

ronman10
New Contributor III

Re: Expression to show YTM data

This worked. Thanks a lot Sunny. Did the * do the trick?

MVP
MVP

Re: Expression to show YTM data

Yes it did... read more about it here

Implicit Set Operators

ronman10
New Contributor III

Re: Expression to show YTM data

Thank you Sunny!

MVP
MVP

Re: Expression to show YTM data

Your welcome