User date selection + YTD calc + fixed max date how?

[rmuhammad]

Hi All

I have a situation where I need to generate pivot table see pic below. When user select any period I need to calc YTD in the Set Analysis I have to have max of SEPN date. I dont know how to write this statement. when I say SEPN ={$(=max(Rpt Yr))} I get 2017 only because Rpt Yr 2016 has no 2017 in SEPN.

Please help

Requirement:

Sample Data: xlsx attached

[felipedl]

Hi Rashid,

See the following QVW for reference.

I just transformed the date into numeral because it's easier to handle in set analysis.

So, if the user gets any date of the month for a specific year, lets say February 2nd, it will get the sum of count field for the year of 2017 and where the month is YTD based on selection.

In this case, it will sum up all the values for January and February 2017.

Left most table is the generated calendar, mid table is the excel data and right most table is the expression used to calculate with set analysis,

Felipe.