Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Compare previous vs current year using set analysis

Hi,

I am new to qliksense. I have to create a table to show previous vs current year comparison. The user should have the ability to use the filters to change the data. 

I have the below filters:

(1) FISCAL_YEAR - FY20, FY19, FY18, FY17

(2) FISCAL_QUARTER - 19Q4,19Q3,19Q2,19Q1,18Q4 etc..

I am trying to use the below set expression to calculate. But this expression is hard coded. 

Current Year: sum({<LANDING_PAGE_SECTION = {"*developers*"}, FISCAL_YEAR={"FY19"}>}SESSIONS) 

Previous Year: sum({<LANDING_PAGE_SECTION = {"*developers*"}, FISCAL_YEAR={"FY18"}>}SESSIONS)Capture1.JPG

 

 

 

 

When the FISCAL_YEAR is FY20 the Current Year column should show FY20 data and Previous Year column should show FY19 data.

7 Replies
Highlighted
Contributor II
Contributor II

Re: Compare previous vs current year using set analysis

I can see the Fiscal Year field is string type. It is better to to have a numeric Year field in the data model and use that for calculation of previous year. Please try below with the given fields, i didn't test though.
Current Year: sum({<LANDING_PAGE_SECTION = {"*developers*"}>}SESSIONS)

Previous Year: sum({<FISCAL_YEAR=, FISCAL_YEAR={$(=purgechar(maxstring(FISCAL_YEAR),'FY')-1)}, LANDING_PAGE_SECTION = {"*developers*"}>}SESSIONS)

Highlighted
Specialist III
Specialist III

Re: Compare previous vs current year using set analysis

HI,

You should at least have a date field in your table/data model. try to create expressions based on that.

Current Year: sum({<LANDING_PAGE_SECTION = {"*developers*"}, FISCAL_YEAR={ " Year(Max(Date))"}>}SESSIONS) 

Current Year: sum({<LANDING_PAGE_SECTION = {"*developers*"}, FISCAL_YEAR={ " Year(Max(Date))-1"}>}SESSIONS) 

 

Thanks 

sasi

Highlighted
Contributor III
Contributor III

Re: Compare previous vs current year using set analysis

Hi Sasi,

I do have  a DATE field. When I used it in the expression you provided it gave me  0.

Just for your information - our Fiscal_Year starts from 1st Feb 

Capture2.JPG

Highlighted
Specialist III
Specialist III

Re: Compare previous vs current year using set analysis

HI can you provide sample app of yours , might be an issue with the format.
Highlighted
Contributor III
Contributor III

Re: Compare previous vs current year using set analysis

Attached is the file

Highlighted
Specialist III
Specialist III

Re: Compare previous vs current year using set analysis

HI,
you should create FIN Calendar using NEWDATE field. please find the below script which will be useful to create FIN calendar.


Let vFM_NO=10
Load
NEWDATE AS DATE_KEY,
Year(NEWDATE ) As CalYear,
Month(NEWDATE ) As CalMonth,
Day (NEWDATE ) as CalDay,
Year(NEWDATE ) + If(Month(NEWDATE )>=$(vFM_NO), 1, 0) as FinYear,
Mod(Month(NEWDATE )-$(vFM_NO), 12)+1 as FinMonth,
'Q'&ceil((Mod(Month(NEWDATE )-$(vFM_NO), 12)+1)/3) as FY_Qrter,

Now use that FinYear in set expressions
Current Year: sum({<LANDING_PAGE_SECTION = {"*developers*"}, FinYear ={ " $(=max(FinYear ))"}>}SESSIONS)

Current Year:
sum({<LANDING_PAGE_SECTION = {"*developers*"}, FISCAL_YEAR={ " Year(Max(Date))-1"}>}SESSIONS)


Highlighted
Contributor III
Contributor III

Re: Compare previous vs current year using set analysis

What does Let vFM_NO=10 mean here? Is it got to do with Start of the month?