Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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)

This is not working anyone please help on this.

Regards,

Pramod

Tags (1)
1 Solution

Accepted Solutions
sunilkumarqv
Valued Contributor II

Re: Last 12 months data

8 Replies

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)


talk is cheap, supply exceeds demand
Not applicable

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

try,

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

Not applicable

Re: Last 12 months data

I am getting all the months not last 12 months.

rsw
Contributor II

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.

Not applicable

Re: Last 12 months data

Hi,

Can you share your qvw file.

sunilkumarqv
Valued Contributor II

Re: Last 12 months data

Not applicable

Re: Last 12 months data

HI all,

Please find sample qvf.

Thanks,

Pramod

Community Browser