Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
In Qlik Sense, I am trying to add the expression to my bar chart to display fiscal year month which is October.
I added the following expression which display the correct month number, for example JAN is showing 4, FEB is showing 5, MARCH is showing 6
=Mod((Month(REGISTERED_DT)+3), 12)
Can someone plz tell me what to write in expression so that it will take OCT, NOV and DEC from 2020 and JAN, FEB, MAR, APR, MAY (current month) from 2021?
Also, I have another Trend analysis chart where I used the drill down dates (Year-Month-Date). I have added the following formula in YEAR-
=IF(IsNull([LAST_UPD_DT]) and MONTH(TODAY())=10,
'FY'&TEXT(YEAR(TODAY()))&'/'&TEXT(YEAR(TODAY())+1),
IF(IsNull([LAST_UPD_DT]) and MONTH(TODAY())<10,
'FY'&TEXT(YEAR(TODAY())),
IF(IsNull([LAST_UPD_DT]) and MONTH(TODAY())>=10,
'FY'&TEXT(YEAR(TODAY())+1),
IF(MONTH([LAST_UPD_DT])<10,'FY'&TEXT(YEAR([LAST_UPD_DT])),
'FY'&TEXT(YEAR([LAST_UPD_DT])+1)))))
It is calculating the correct FY year as FY2020, FY2021 and so on, but when I drill down with Month, it starts with Jan instead on Oct.
Can someone please help me how I can get the correct order of FY month calculation from OCT instead on JAN?
In my data set, all dates are stored in format of - DD-MMM-YY
Thank you all in advance.
I believe the best solution is a Master Calendar , rather than a calculation.
There is no other way to achieve the result I am trying to?
If you data is from Oct 2020 through May 2021, you could sort by expression
Year(LAST_UPD_DT)*100+Num#(Month(Last_UPD_DT))
But a master calendar will offer so much more flexibility.