Last 12 months data

[#1]

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)

This is not working anyone please help on this.

Regards,

Pramod

[Gysbert_Wassenaar]

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)

[#3]

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

[Kushal_Chawda]

try,

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

[#5]

I am getting all the months not last 12 months.

[Anonymous]

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.

[#7]

Hi,

Can you share your qvw file.

[sunilkumarqv · Accepted solution]

Helps you

Relative Calendar Fields

[#9]

HI all,

Please find sample qvf.

Thanks,

Pramod