Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
rmuhammad
Contributor

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

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:

yr2.PNG

Sample Data: xlsx attached

yr1.PNG

1 Reply
Partner
Partner

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

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,

Sample.png

Felipe.