Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Last 6 month for current year and Last year

Hi 

I have 2 fields "CreationDate" and "Sales". 

I need the sum of sales for last 6 months, excluding current month, that means Oct is current month. I need to calculate from Apr to Sep and same Apr to Sep for last year also. 

What would be set analysis expression?

Labels (1)
1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

Re: Last 6 month for current year and Last year

Hi,

Try these expressions

Last 6 months current year (Apr-Sep 2019)

=Sum({<CreationDate={">=$(=AddMonths(MonthStart(Today()),-6)) <$(=MonthStart(Today()))"}>} Sales)

Last 6 months last year (Apr-Sep 2018)

=Sum({<CreationDate={">=$(=AddMonths(MonthStart(AddYears(Today(),-1)),-6)) <$(=MonthStart(AddYears(Today(),-1)))"}>} Sales)

View solution in original post

4 Replies
Highlighted
Partner
Partner

Re: Last 6 month for current year and Last year

Hi There,

First you need to create fiscal calendar from your CreationDate and the need to refer FiscalMonth and FiscalYear fields in set expression like below - 
Previous Year Apr - Sep  

sum({$<FiscalYear  = {"$(=(max(FiscalYear))-1)"} , FiscalMonth = -  {"$(=month(today()))"} >} Sales)

Current Year Apr - Sep  

sum({$<FiscalYear  = {"$(=Max(FiscalYear  ))"}, FiscalMonth =- {"$(=month(today()))"} >} Sales)

 

Thanks,

Rahul R

Highlighted
Creator III
Creator III

Re: Last 6 month for current year and Last year

Hi,

Try these expressions

Last 6 months current year (Apr-Sep 2019)

=Sum({<CreationDate={">=$(=AddMonths(MonthStart(Today()),-6)) <$(=MonthStart(Today()))"}>} Sales)

Last 6 months last year (Apr-Sep 2018)

=Sum({<CreationDate={">=$(=AddMonths(MonthStart(AddYears(Today(),-1)),-6)) <$(=MonthStart(AddYears(Today(),-1)))"}>} Sales)

View solution in original post

Highlighted
Creator III
Creator III

Re: Last 6 month for current year and Last year

Hi 

This is working perfectly fine.

Thanks  

Highlighted
Contributor II
Contributor II

Re: Last 6 month for current year and Last year

Hi

these two work perfectly by themselves, but I want to try

Last 6 months current year (Apr-Sep 2019)/Last 6 months last year (Apr-Sep 2018)

to show like Sep19/Sep 18 in growth rate, any idea? Many thanks