Show last 3 months data based on filter selected

[roebrich]

Hello,

I have a requirement to show the last 3 months data based on the filter selected. By default, when the user opens the dashboard it will be filtered for the current month so the chart should show: current month, current month -1, and current month -2. Right now what I have implemented is it will show the last 3 months starting from the current month if no date is selected. but if a date is selected it will only show that date/month.

I attached a sample qvw for easier reference.

Thank you so much in advance!

[sunny_talwar · Accepted solution]

Try this

Count({$<MONTH_NUMERIC = {">=$(=vMaxMonth-2)<=$(=vMaxMonth)"}, Month>} Number)

[roebrich]

Thank you so much! It worked showing last 3 months based on the data. However for the selection of Jun 2017, it shows Apr 2016. I know the data has a lot of blanks, but the expectation there is if there is no data for the months before it should still be plotted but as 0 values. For example if Jun 2017 was selected we will show in the chart Jun 2017 and May 2017 with values and Apr 2017 with 0 as the value.

Thanks!

[roebrich]

Please disregard my previous comment. I have confirmed that that scenario is not a possibility with live data. Although if you have some free time I would very much appreciate if you could still help me solve that scenario.

Thank you so much!

[sunny_talwar]

Created like this

NUM(MONTHName(opened_at_dttm)) AS MONTH_NUMERIC,

and then this expression

Count({$<MONTH_NUMERIC = {">=$(=Num(AddMonths(Max(MONTH_NUMERIC), -2)))<=$(=Num(Max(MONTH_NUMERIC)))"}, Month>} Number)

[triekong]

stalwar1‌

i have implemented this solution in my report. but i have an issue with the years on the report. i have data from Dec2016, to Dec2018.

when i select the Month Year of "December 2017" for example, i want the data to go back 12months from then.

Using the expression above, expected result is : Dec2017,Nov2017,Oct2017,Sept2017,Aug2017,Jul2017,Jun2017,May2017,Apr2017,Mar2017,Feb2017,Jan2017.

Instead,the expression seems to be including both "December2016" and "December2017", and "January2017" and "January2018" on the chart, irrespective of what year it they are in. so the data now goes from Dec2016 to Jan2018.

how can i handle this? thanks

[sunny_talwar]

Try this expression

Sum({$<MonthYear = {">=$(=Date(AddMonths(Max(MonthYear), -11), 'MMM-YYYY'))<=$(=Date(Max(MonthYear), 'MMM-YYYY'))"}, Month, [Question Category]= {'Overall Customer Satisfaction'}>} [Question Score])