Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

How to get last 12 months only? Need expression for Dimension rather than Expression

Hello,

I need to create a filter for my dimension where it should only show data from the past 12 months from the current month.

Anyone know how to do this?

It is easy to do in set analysis but I need it for my Dimension instead and not measure.

So in essence a Dimension restricted to the last 12 months values only.

1 Solution

Accepted Solutions
Highlighted

Re: How to get last 12 months only? Need expression for Dimension rather than Expression

Use this as your calculated dimension

If(Date >= AddMonths(Today(), -12), Date)

View solution in original post

4 Replies
Highlighted

Re: How to get last 12 months only? Need expression for Dimension rather than Expression

May be like this

If(Date >= AddMonths(Max(TOTAL Date), -12), Date)

and then make sure to select 'Suppress when value is Null'

To make it work with selections, make sure to ignore Date field in set analysis and let your dimension drive selections and range

Sum({<Date>} Measure)

Highlighted
Creator II
Creator II

Re: How to get last 12 months only? Need expression for Dimension rather than Expression

Hi Sunny,

Thank you,

How can I do this where I can say Date = only dates from the past 12 months from today

It's the expression after the = that I need. I am not sure whether it should be an = or >= or <=.

Highlighted

Re: How to get last 12 months only? Need expression for Dimension rather than Expression

Use this as your calculated dimension

If(Date >= AddMonths(Today(), -12), Date)

View solution in original post

Highlighted
Contributor II
Contributor II

Re: How to get last 12 months only? Need expression for Dimension rather than Expression

Hi Sunny, 

I have been trying to do the same. If i don't select any date, it loads the last 12 months from the current month. But it breaks if i select any Date. My requirement is, if i  don't select any date, my table should display the last 12 month wrt the current month but if any date is selected, it should display the last 12 months from that selected date. The expression used in my table are: 

Date : =If(Date >= AddMonths(Max(TOTAL Date), -12), Date)

NAV : Num(Sum(Aggr(Sum(nav), Date))/1e9,'#,###')