Limit a dimension to only show the last X months to date?

[#1]

Hi all,

I have a bar graph that is showing actual sales compared to Budgeted sales.

I want the graph to only show the last X months to date. By this I mean in June 2015, I want to see the last 12 months up to June 2015, which is June 2014 to June 2015, even if my budget data is going up to December 2015.

I have a master calendar in which I have Year, Month and YearMonth fields I can use.

My bar graph is using YearMonth Dimension.

I thought I should use Dimension limitation by fixed number and set an expression like this : YearMonth={">$(=Max(YearMonth)-12) <=$(=Max(YearMonth))"}>}, but it didn’t work.

I digged around on post about last x months but they are mostly for QlikView while I’m using Qlik Sense.

Thanks in advance for your help.

  Patrick

[sunny_talwar]

Try this:

YearMonth={">$(=MonthName(AddYears(Max(YearMonth), -1)), <= $(=MonthName(Max(YearMonth)))"}>}

here I am assuming thet YearMonth is having formated in the script using MonthName() function. If it is not, you will need to make sure that expression within the curly brackets have the same format as YearMonth.

HTH

Best,

Sunny

[#3]

Hi Sunny! Many thanks for taking time to help community. I wish I’ll be able one day to help as well…

Unfortunately, I don't understand your expression and how to use it.

First, I didn't use the Monthname() function. I used dual(Year(TempDate) & Month(TempDate), Year(TempDate) & num(Month(TempDate))) as YearMonth with Dual() function in order to sort Month by numeric value. Maybe it could be done smarter...

Anyway, I'm not sure where I should use this expression... Should I use it in the limitation area of the YearMonth dimension ? See screenshot below (sorry, French language… J)

And I don’t understand how this expression will tell to the graph what is the “end date” part and what is the “start date” part?

In the end, as you can see in the screenshot, using your expression in the limitation area of the dimension as given me only one bar for October 2015.

I’m clearly missing some things…

If you can take few more minutes to drive me through or indicate me some readings.

Thanks in advance.

Patrick

[#4]

Hi all,

I finally found a solution to my problem. Maybe not the most elegant one, but at least it did work. And for newbie like me, it might save quite some time.

As reminder, I wanted to restrict the bar graph to the last X months of actual sales and restrict the Budgeted sales to not display budget for month greater than current month. Nothing exotic I would say.

I thought initially that I should use a limitation in the Dimension I used (YearMonth). But this doesn't allow to do what I wanted.

.

After digging around and a lot of try, I found that I had to use the Measures of my bar graph. Maybe that sounds obvious but it wasn't for me...

Finally, here are the expressions I used:

• To limit actual sales to 12 months : Sum({$<InvoiceDate = {">=$(=date(today()-365))"}>}[Invoice Amount])
• To limit budgeted sales to 12 months and to not show data greater than today: sum({$<BudgetDate = {">=$(=date(today()-370)) <=$(=today())"}>}MontantBudget)

Here is the result:

I'm sure there are smarter ways to achieve this, and will try on my own.

Hope this will help someone.

Best
Patrick