Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator
Creator

Populate result for latest 12 month

I want to show only latest 12 Month. But it show all data. Please help.

This is my dimension:

Month

This is my expression for measure:

sum({$<Month={"$(='>=' & Max(Month-13) & '<=' & Max(Month))"}>} [Number of Project])


Capture.JPG

1 Solution

Accepted Solutions
Specialist III
Specialist III

Try this:

sum({$<Month={">=$(=AddMonths (Max(Month),-11))"}>} [Number of Project])


Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago

View solution in original post

3 Replies
Master II
Master II

Hi,

try this

step1 : convert date in load script

load *, Date(Date#(Month,'M/D/YYYY'),'D/M/YYYY') as Date;

LOAD * INLINE [

    Month, Number of Project

    1/1/2017, 10

    2/1/2017, 13

    3/1/2017, 10

    4/1/2017, 21

    5/1/2017, 10

    6/1/2017, 21

    7/1/2017, 10

    8/1/2017, 21

    9/1/2017, 21

    10/1/2017, 10

    11/1/2017, 21

    12/1/2017, 21

    1/1/2018, 10

    2/1/2018, 21

    3/1/2018, 13 

];

2) use below set expression

Sum({$<Date={">=$(=MonthStart(Max(Date), -11))<=$(=Date(Max(Date)))"}>} [Number of Project])

above expression will return current + last 11 month

note: if you wish have only last 12 month and not including the current month. then try to adjust the lesser than operation like below

Sum({$<Date={">=$(=MonthStart(Max(Date), -12))<$(=Date(Max(Date)))"}>} [Number of Project])

Capture.JPG

Contributor
Contributor

Change this Max(Month-13) for Max(Month)-13

Specialist III
Specialist III

Try this:

sum({$<Month={">=$(=AddMonths (Max(Month),-11))"}>} [Number of Project])


Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago

View solution in original post