topic Re: how to filter the date in Reporting Service & Alerting

how to filter the date

qlick_qa1 — Fri, 15 Nov 2024 21:15:40 GMT

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 ?

Re: how to filter the date

sidhiq91 — Sat, 25 Nov 2023 11:03:17 GMT

@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.

Re: how to filter the date

Lech_Miszkiewicz — Tue, 26 Nov 2024 10:59:28 GMT

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