Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi , I am trying to show only the last 2 years data in qlik sense chart. but some how I am unable to write the expression correctly which I could get from the community.
Currently the expression shows all the year .I tried to change the expression as below.but it gives error. Could anyone help .
{<FY={$(=Max(Journal_Date.FiscalCalendar.FY)),$(=Max(Journal_Date.FiscalCalendar.FY)-1)}>}
this is working fine, but you need to add in in your current expression!!
example
=sum({<
Classification={'SBU'}
,Account_Code-={'6*','7*','8*'}
,[Journal_Date.FiscalCalendar.Fiscal Year] ={">=$(=Max([Journal_Date.FiscalCalendar.Fiscal Year] ,2))<=$(=Max([Journal_Date.FiscalCalendar.Fiscal Year] ))"}
>}[Deficit Surplus])
as below
sum({<FY={">=$(=Max(Journal_Date.FiscalCalendar.FY,2)"}>} somemeasure)
or
sum({<FY={">=$(=Max(Journal_Date.FiscalCalendar.FY,2)<=$(=Max(Journal_Date.FiscalCalendar.FY)"}>} somemeasure)
or
sum({<FY={'$(=Max(Journal_Date.FiscalCalendar.FY,2)','$(=Max(Journal_Date.FiscalCalendar.FY)'}>} somemeasure)
@vinieme12 thanks for the solution provided.
bocoz my x axis is the dimension as shown below
=If([Journal_Date.FiscalCalendar.FY]<>'FY', [Journal_Date.FiscalCalendar.FY])
and based on your solution it asks to write the 'somemeasure' .Which will be the measure that I need to write over here?
or is it possible to have without the measure?
Dimension should be
=Journal_Date.FiscalCalendar.FY
Measures
=sum({<FY={">=$(=Max(Journal_Date.FiscalCalendar.FY,2)"}>} somemeasure)
The measure will limit the dimension values,
What is the format of the field - Journal_Date.FiscalCalendar.FY??
@vinieme12 let me try to resolve it with the current hint provided
.The format of Journal_Date.FiscalCalendar.FY is - (FY20).
if Journal_Date.FiscalCalendar.FY is is FY20, then this is a text field
You cannot add/subtract on a text field, you need a field with numeric values
2021,2022, etc
@vinieme12 ok tks. Got it .But if I want to show in the x axis with Fiscal Year but only 2 years is there any suggestion for a workaround.
and also I do have 2 dimensions inside and also 1 measure as shown in the picture. If I need to add the measures as suggested by you how is it possible and also what will be the 'somemeasure' in this case.
Thanks
Refer this article on how to create fiscal calendar along with standard calendar
=sum({<FY={">=$(=Max(Journal_Date.FiscalCalendar.FY,2)"}>} somemeasure)
somemeasure <<-- is the Numeric field that needs to be aggregated
@vinieme12 . Could I ask you one more doubt.I do have a fiscal calendar created in my script . and that is being used in the charts.
Even if I create a new one ,it would be still text format right? so then, I won't be able to use it in the expression, right?
Can you paste the script you are using to create the fiscal calendar
you can use Dual() to assign a text representation to a numerical year field
or have two fields FiscalYearText with values FY20,FY21,Fy22 and another field FiscalYear 2020,2021,2022 etc