Compare previous vs current year using set analysis

[ajinkyabhonsle]

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)

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

[shovon_ra]

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]

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]

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 

[sasikanth]

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

[ajinkyabhonsle]

Attached is the file

[sasikanth]

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]

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