Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
abc_18
Creator II
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
Vegar
Partner
Partner

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)

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

7 Replies
JustinDallas
Specialist II
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.

 

abc_18
Creator II
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

abc_18
Creator II
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
*;
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

Vegar
Partner
Partner

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
abc_18
Creator II
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.

Please suggest

Vegar
Partner
Partner

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)

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

abc_18
Creator II
Creator II
Author

Hi Vegar,

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

 

Regards