Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
ajinkyabhonsle
New Contributor II

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
shovon_ra
New 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)

sasikanth
Valued Contributor 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

ajinkyabhonsle
New Contributor II

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

sasikanth
Valued Contributor 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.
ajinkyabhonsle
New Contributor II

Re: Compare previous vs current year using set analysis

Attached is the file

sasikanth
Valued Contributor 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)


ajinkyabhonsle
New Contributor II

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?