topic Re: how to show latest 24 months of data in chart? in QlikView

how to show latest 24 months of data in chart?

udaya_kumar — Sun, 15 Jul 2012 08:09:42 GMT

Hi,

I have a date field in the chart as x-axis.

Currently am showing 72 months of data, the full data.

now i want to show only latest 24 months of data in the x-axis in the chart.

how we can do that?

Re: how to show latest 24 months of data in chart?

Jason_Michaelides — Sun, 15 Jul 2012 08:13:36 GMT

Use set analysis. Say your current expression is Sum(Sales). Instead, try:

Sum({<Date={">=$(=AddMonths(Max(Date),-24))"}>} Sales)

Hope this helps,

Jason

Re: how to show latest 24 months of data in chart?

Jason_Michaelides — Sun, 15 Jul 2012 08:14:05 GMT

(Or if you are using verson 11 then use dimension limits.)

Re: how to show latest 24 months of data in chart?

udaya_kumar — Sun, 15 Jul 2012 08:47:56 GMT

Hi Jason,

I am using qlikview 11, so it works.

but i want to show 72 months data in the same chart also.

By default, i should show 24months data, and i have a checkbox,

clicking on checkbox, it should show full 72 months data.

This is the problem.

Re: how to show latest 24 months of data in chart?

Jason_Michaelides — Sun, 15 Jul 2012 09:12:50 GMT

You could just use a variable in the dimension limits box. Have a variable vDateRange set by a checkbox/inputbox and put vDateRange into the dimension limits section.

Re: how to show latest 24 months of data in chart?

udaya_kumar — Sun, 15 Jul 2012 09:43:26 GMT

Hi Jason,

Thanks for the answer, it works fine,

but how can i do this for pivot table?

In Pivot table, dimension limit tab is not there.

Please help.

Re: how to show latest 24 months of data in chart?

Jason_Michaelides — Sun, 15 Jul 2012 12:33:00 GMT

I think you'll need to use my expression suggestion above, replacing -24 with vDateRange.

Re: how to show latest 24 months of data in chart?

udaya_kumar — Sun, 15 Jul 2012 12:36:15 GMT

Jason,

I need to use my field in place of Date in the above expression right?

I use it like that, but i didnt get the result.

Re: how to show latest 24 months of data in chart?

udaya_kumar — Sun, 15 Jul 2012 12:39:07 GMT

Jason,

My Date field is of MMM YYYY format, but i checked this expression

(=AddMonths(Max(Date),-24))

I am getting 5/1/2012

but the data for month, I converted that into May 2012 format.

Is this creating problem for that?

Re: how to show latest 24 months of data in chart?

Jason_Michaelides — Sun, 15 Jul 2012 12:43:51 GMT

Try:

Sum({<Date={">=$(=Date(AddMonths(Max(Date),-24),'MMM YYYY')"}>} Sales)

Re: how to show latest 24 months of data in chart?

udaya_kumar — Sun, 15 Jul 2012 13:04:54 GMT

Jason,

It is not working

This is the expression am using

sum({<MONTH_START_DATE={"<=$(=Date(AddMonths(Max(MONTH_START_DATE),-vMonth),'MMM YYYY')"}>}TRx_Volume)

What is the problem here?

Re: how to show latest 24 months of data in chart?

udaya_kumar — Sun, 15 Jul 2012 13:08:04 GMT

It is showing,

No Data to Display on the table.

only({<MONTH_START_DATE={">=$(=Date(AddMonths(Max(MONTH_START_DATE),-vMonth),'MMM YYYY')"}>}TRx_Volume)

This is the expression i tried, but also not working

Re: how to show latest 24 months of data in chart?

Jason_Michaelides — Sun, 15 Jul 2012 14:39:25 GMT

Post your app and I'll take a look.