topic Re: YTD Calculation set expression in App Development

YTD Calculation set expression

GrantBooth — Fri, 20 May 2022 01:59:22 GMT

I am trying to calculate cumulative year to date within an app for a financial year from July to June.

My data contains multiple financial years and already contains a field that maps the date to the financial year.

The date field is month end date, which takes the values 31/07/2021, 31/08/2021 etc.

Ideally I am looking for a set expression that looks for the sum of all values up to the selected date. So if a user selects FY22 and month August, the script would be something like

sum( {Set expression for July} to {Selected Month} value)

Could anyone please offer some help here?

Thanks

Re: YTD Calculation set expression

vinieme12 — Fri, 20 May 2022 09:19:51 GMT

As below

sum({<Datefield={">=$(=Date(yearstart(Max(Datefield),0,7),'YYYY-MM-DD'))<=$(=Date(Max(Datefield),'YYYY-MM-DD'))"}>}Amount)

replace YYYY-MM-DD with the dateformat of your datefield

refer these posts

https://community.qlik.com/t5/Qlik-Sense-Documents/Master-Calendar-Script-with-Optional-Fiscal-Year/ta-p/1692173

https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/DateAndTimeFunctions/yearstart.htm

https://community.qlik.com/t5/Qlik-Design-Blog/Dates-in-Set-Analysis/ba-p/1472511

Re: YTD Calculation set expression

GrantBooth — Sun, 22 May 2022 22:37:35 GMT

Thanks mate that was exactly what I was after