8 Replies Latest reply: Sep 18, 2015 2:25 AM by Kumar Pramod

# Last 12 months data

Hi all,

I am displaying the data according to months from 2012 but now i need only last 12 months excluding the present month.

In data load i am using the below month function:

MonthName(date_created) as Month_Year.

In dimension i am using : Month_Year.

In Measures : count({<Month_year={">\$(=date(max(Month_year)-13))<=\$(=date(max(Month_year)-1))"}>}user_signed)

Regards,

Pramod

• ###### Re: Last 12 months data

Month_year is a date. The unit of a date field is a day. If you subtract 13 from a date then you get a date 13 days earlier. You want to use the monthstart function in this case:

count({<Month_year={">\$(=monthstart(max(Month_year),-13))<\$(=monthstart(max(Month_year)))"}>}user_signed)

• ###### Re: Last 12 months data

Hi Gysbert Wassenaar,

Month_year is not a date. it is like (May-2014, Apr-2014).

The above script is not working.

I think as the Month_Year filed is Alpha numeric i am not able to subtract from it. so i need to convert it to numbers.

can you explain how can i achieve?

thanks,

Pramod

• ###### Re: Last 12 months data

Hi Kumar,

Gysbert is correct and you may trust that with his experience he is very unlikely to get something like this wrong. Did you try his solution?

To prove to yourself what is happening simply put your Month_year column in a table and then put "=Date(Month_name -13)" in another column. Then try a third column with just "=Month_name-13". You will notice that while your Month_name column is being presented as a combination of month and year, it is in fact stored as a dual (you can confirm that by checking the help on the monthname function) with the full date recorded in number form, so it is a date as Gysbert has said.

• ###### Re: Last 12 months data

try,

rangesum(above(count(user_signed),0,12))

• ###### Re: Last 12 months data

I am getting all the months not last 12 months.

• ###### Re: Last 12 months data

Hi,

Can you share your qvw file.

• ###### Re: Last 12 months data

HI all,