how to filter the date

[qlick_qa1]

Hi, for a new line chart, i connected a database that has date dimention and  it is refreshing daily.  It covers past years till the day before. I want the line chart to show just the last 28 days. How can i filter the last 28 days ?   

[sidhiq91]

@qlick_qa1  Please use the below expression in the measure.

Sum({<Date={">=$(=Date(Max(Date)-28,'MM/DD/YYYY'))<$(=Max(Date))"}>}Sales)

If this resolves your issue please like and accept it as a solution.

[Lech_Miszkiewicz]

Hi @qlick_qa1

yes- you can use set analysis and the $ expansion in set analysis but much better practice is just to flag those last 28 days as a flag in load script and use the flag in set analysis:

• In load script:
  • If(yourdate>=Today()-28 and yourdate<=Today(),1,0) as fLast28Days
• In chart expression:
  • Sum({<fLast28Days={1}>}Sales)

That way you will avoid issues with date format in set analysis as well as the actual date refrence. We dont know your data, but if your date field allows for records from future your "Max(Date)" will not provide you correct date ranges for last 28 days - so consider that as well.

cheers