Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
blakeB
Contributor II
Contributor II

Display last 12 months in bar chart

Hi All,

I have seen several postes about displaying a date range, but am still having some trouble with it and was hoping for some help. I have a bar chart, using Month.YearMonth as the dimension and TotalSales as the measure. Want to limit the dates to on the x-axis to only show the last twelve months or 2019-present.

Do I put a calculated expression as the dimension, and remove the Month.YearMonth dimension? Or is there a way to write an expression in the limitation field (Exact value).

 

4 Replies
vvira1316
Specialist II
Specialist II

radmin5253
Contributor III
Contributor III

I had a requirement for this and solved it by adding the following line to the end of my load script it creates a dimension called _Rolling12 that can be then be used anywhere in the application. (In my case my metric was called PageViews so replace it with your metric)

If( Num(Date) > addmonths($(CurrentDate),-12) and Num(Date) <= Today(),1) as _Rolling12

Then in my bar chart I used the following formula in my metric

Sum(PageViews*_Rolling12)

as my Dimension I used a combination of Year Month from my Master Calendar and in the add-ons section of the bar chart options I unchecked the box that says Include zero values so it only shows the last 12 months and not the months with no totals.

blakeB
Contributor II
Contributor II
Author

For some reason when I input the string you provided, it errors out. Am I missing something?

If( Num(Date) > addmonths($(CurrentDate),-12) and Num(Date) <= Today(),1) as _Rolling12
radmin5253
Contributor III
Contributor III

Sorry you need to create a variable in the load script for the current date add this line prior to loading the previous line I posted

 

Let CurrentDate=num(date(Today(),'YYYYMMDD'));

 

This should do it but if not let me know, the entire script I use is much more fleshed out and was shown to me at a Qlik Conference I attended about a year or so ago.