

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
YTD and Previous YTD based on fiscal year
Hi,
My fiscal year starts from April.
I'm trying to figure out a simple YTD and previous YTD expression:
The year follows the Fiscal year, so I'm looking for FY 2020 (19/20) 2019-04-01 through 2020-01-27(till yesterday) in comparison to FY 2019 (18/19) 2018-04-01 through 2019-01-28.
Regards
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You are absolutely correct. I'm sorry but I must have missread your question.
Try these expressions instead.
YTD:
=Sum({<
fYear,FMonth,Month, Year, invoice_date={">=$(=YearStart(max(invoice_date),0,4))<$(= addyears(max(invoice_date), 0))"}
>}Sales)
Previous YTD:
=Sum({<
fYear,FMonth,Month,Year,invoice_date={">=$(=YearStart(max(invoice_date),-1,4))<$(= addyears(max(invoice_date), -1))"}
>}Sales)
Qlik Community MVP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What are you trying to compare? A bar chart, a line chart and so on. Generally, you have a Master Calendar or a flag that shows whether or not the date concerned is in the current fiscal year or the previous year.
LET vToday = Daystart(Timestamp(Today()))
;
MasterCal:
...
If( InYear (TempDate, '$(vToday)',0),'Y','N') as CY,
If( InYear (TempDate, '$(vToday)',-1),'Y','N') as LY,
...
Then, you would use Set Analysis to figure compare counts between the current year and the previous year.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Justin,
I have to show current YTD and previous YTD in KPI.
So first KPI should show current YTD and another one previous YTD, and that includes fmonth, fyear and date selection as well.
I have a calendar script already which includes fiscal year description.
I am trying to achieve these two KPI's using set analysis.
Thanks


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am using below expression to achieve this:-
sum({<invoice_date=,fYear=,FMonth=, invoice_date={">=$(=YearStart(Max(invoice_date)))<=$(=Max(invoice_date))"}>}qty)
This gives me 2020-01-01 till 2020-01-28, but the output should be 2019-04-01 till 2020-01-28, and for previous 2018-04-01 till 2019-01-28, based on fiscal year.
below is the calendar script which I am using:-
Set vFM = 4 ; // First month of fiscal year
Calendar:
Load Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year
Dual(Month, fMonth) as FMonth, // Dual fiscal month
*;
Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month
*;
Load Year(invoice_date) as Year, // Your standard master calendar
Month(invoice_date) as Month,
num(Month(invoice_date)) as monthno,
Date(Monthstart(invoice_date), 'MMM-YYYY') as MonthYear,
invoice_date
Resident XYZ;
This invoice_date field is linked to data model .
Regards

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You could try something like this. Assumming you want to calculate fiscal year based on the in max(invoice_date) in your selection.
Last f year
=Sum({<fYear,FMonth,Year,Month,invoice_date={">=$(=YearStart(max(invoice_date),-1,4))<$(=YearEnd(max(Date),-1,4))"}
>}Amount)
This f year
=Sum({<fYear,FMonth,Year,Month,invoice_date={">=$(=YearStart(max(invoice_date),0,4))<$(=YearEnd(max(Date),0,4))"}
>}Amount)
Qlik Community MVP


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Vegar,
Thanku very much for quick response.
I tried the expression suggested by you:-
=Sum({<fYear,FMonth,Month,invoice_date={">=$(=YearStart(max(invoice_date),0,4))<$(=YearEnd(max(invoice_date),0,4))"}
>}qty)
but this gives me date range 2019-04-01 to 2020-03-31, the till data should be till yesterday that is 2020-01-28
similarly for previous year till date should be 2019-01-28, currently it's giving 2019-03-31.
Please suggest

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You are absolutely correct. I'm sorry but I must have missread your question.
Try these expressions instead.
YTD:
=Sum({<
fYear,FMonth,Month, Year, invoice_date={">=$(=YearStart(max(invoice_date),0,4))<$(= addyears(max(invoice_date), 0))"}
>}Sales)
Previous YTD:
=Sum({<
fYear,FMonth,Month,Year,invoice_date={">=$(=YearStart(max(invoice_date),-1,4))<$(= addyears(max(invoice_date), -1))"}
>}Sales)
Qlik Community MVP


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Vegar,
Thank you very much for help 🙂 , working as expected.
Regards
