Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am new to Qlik Sense. I have a data set where I have transactions and fiscal years like 2016-2017, 2017-2018 etc...and each transaction is having a date. And my fiscal year starts with April and ends with March. So What I want to show in Qlik Sense is, if I select a year in filter as 2017-2018 then It should show me the Quantity of April 2017- March 2018 in one KPI and Quantity of Previous year that is April 2016- March 2017 in other KPI as Previous Year. So how can i approach this? I have used sum(QTY) in 1st KPI but how about Previous Year's KPI ? How to write set analysis expression for that ? My fields are Financial Year, QTY which I want show in the end. So Basically I need two KPI's- one for Current Selected Year and One for Previous Year as selected in filter. Note that I have 2016-2017, 2017-2018 values as my Financial Year field not like 2016, 2017....Thanks in advance.
Assuming you want to calculate SUM(Amount) and have a date field called [Date] then try something like this.
This fiscal year
Sum({<FiscYear=, [Date] = {">$(=yearstart(max([Date]), 0,4)) <$(=yearend(max([Date]), 0,4)) "} >} Amount)
Previous f year
Sum({< FiscYear=, [Date] = {">$(=yearstart(max([Date]), - 1,4)) <$(=yearend(max([Date]), - 1,4)) "} >} Amount)
Assuming you want to calculate SUM(Amount) and have a date field called [Date] then try something like this.
This fiscal year
Sum({<FiscYear=, [Date] = {">$(=yearstart(max([Date]), 0,4)) <$(=yearend(max([Date]), 0,4)) "} >} Amount)
Previous f year
Sum({< FiscYear=, [Date] = {">$(=yearstart(max([Date]), - 1,4)) <$(=yearend(max([Date]), - 1,4)) "} >} Amount)
Thanks a lot Vegar, it is working.
Hi Vegar, in addition to yesterday's post I have one more filter as month. It is showing the specific values particularly present in that month. But somehow if i am selecting July month then it is showing me April month's values. It is showing me the month values as(selected month-3). I haven't changed any format or expressions to get this output. Attaching some photos of it if that can help. Thanks
It's hard to pinpoint by the images you posted, but my best guess is that it has to do with your fiscal month. I notice that April is sorted first and I assume it got the numeric numeric value of one, July would then be four which would be equivalent to April in a normal calendar year.
What do you think about my theory? Is this pointing you into a solving direction?
I got your point. How should I change it then? Because I haven't done the Fiscal Month calculation anywhere just tried FirstMonthOfTheYear=4 in our main script. I am attaching some photos of main and auto generated scripts, so you can get a better idea of it. Thanks for your quick response and looking forward to get an answer from you. Thanks
Hi Vegar,
I tried your formula for FY Sum({< FiscYear=, [Date] = {">$(=yearstart(max([Date]), - 1,4)) <$(=yearend(max([Date]), - 1,4)) "} >} Amount)
but got the result of Jan22.
Can you share inputs please? Thanks a lot.
Kenji
do you make any master calendar selections other than FiscYear and Date? Eg [Month] and [Week]. If so then you would need to clear them out with additional modifiers like this:
Sum({< FiscYear=, Month=, Week=,[Date] = {">$(=yearstart(max([Date]), - 1,4)) <$(=yearend(max([Date]), - 1,4)) "} >} Amount)