Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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
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
Attached is the file
What does Let vFM_NO=10 mean here? Is it got to do with Start of the month?