Expression to show YTM data

[ronman10]

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

[sunny_talwar]

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]

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

[sunny_talwar · Accepted solution]

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]

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

[sunny_talwar]

Yes it did... read more about it here

Implicit Set Operators

[ronman10]

Thank you Sunny!

[sunny_talwar]

Your welcome