Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jhammarquez
New Contributor

Show Past 12 Months trend in Qlik Sense line chart

Hi,

I want to create a line chart which will show the trend for the past 12 months based on the selected month from the filter pane. My date is based on a fiscal year and currently I am only able to show the trend in 1 fiscal period (e.g. if I select June 2018, it will show the trend from April 2018-June 2018)

However, I would like to always show the past 12 months trend (e.g.if I select June, it will show the trend from July 2017-June 2018)

Fiscal period: April - March

Below is the code that I used:

Dimension:

date(('01/' &

  if([GL.FiscalMonth] > 9, ([GL.FiscalMonth]-9),[GL.FiscalMonth] + 3) & '/' &

    if([GL.FiscalMonth] > 9, ([GL.FiscalYear] + 1),[GL.FiscalYear])),'MMM-YYYY')

Measure:

sum({<[GL.FiscalYear]= {'$(=(vSelYr))'}, [GL.FiscalMonth] = {'<=$(=Max(vSelMon)) >=$(=AddMonths(Max(vSelMon),-11))'}>}debit_credit_amount)

I'm not sure which part is wrong. Appreciate any help

Thank you!

1 Reply
arvind654
Honored Contributor II

Re: Show Past 12 Months trend in Qlik Sense line chart

Maybe use below expression?

sum({<

YourDateFieldHere = {"$(='>=' & Date(AddYears(Max(YourDateFieldHere), -1), 'MM/DD/YYYY')

& '<=' & Date(Max(YourDateFieldHere), 'MM/DD/YYYY'))"}, Month, Year, Quarter, Week

}>}debit_credit_amount)


Make sure to check your Date format is as what I have in above expression plus replace yourdatefield...