# App Development

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for
Did you mean:
Creator II

## 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

1 Solution

Accepted Solutions
MVP

You are absolutely correct. I'm sorry but I must have missread your question.

YTD:
=Sum({<
>}Sales)

Previous YTD:
=Sum({<
>}Sales)

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
7 Replies
Specialist II

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.

Creator II
Author

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

Creator II
Author

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
*;
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

MVP

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)

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Creator II
Author

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.

MVP

You are absolutely correct. I'm sorry but I must have missread your question.

YTD:
=Sum({<
>}Sales)

Previous YTD:
=Sum({<
>}Sales)

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Creator II
Author

Hi Vegar,

Thank you very much for help 🙂  , working as expected.

Regards

Tags
Community Browser