topic Re: Showing only last 12 month values in a chart in QlikView

Showing only last 12 month values in a chart

rsdhavle — Thu, 07 Aug 2014 07:15:32 GMT

I have month dimension with values jan, feb, mar etc. I have calculate lets say revenue and show it only for last 12 months inside bar chart, how can i use Addmonths in this case. i have date column on the basis of which i can calculate latest month

Re: Showing only last 12 month values in a chart

kaushiknsolanki — Thu, 07 Aug 2014 07:19:00 GMT

Hi,

Use below set analysis

Sum({<Date = {">=$(=Monthstart(addmonths(Max(Date),-12)))"}>}Sales)

Regards,

Kaushik Solanki

Re: Showing only last 12 month values in a chart

Gysbert_Wassenaar — Thu, 07 Aug 2014 07:19:53 GMT

Perhaps sum({<MyDate={'>$(=monthstart(max(MyDate),-12))'}>}MyAmount)

Re: Showing only last 12 month values in a chart

rsdhavle — Thu, 07 Aug 2014 07:29:07 GMT

I am still not getting any result. I am passing Month as a dimension and above expression sum({<Date={'>$(=addmonths(monthstart(max(Date)),-12))'}>}Revenue)

I am missing on anything?

Re: Showing only last 12 month values in a chart

kaushiknsolanki — Thu, 07 Aug 2014 07:32:44 GMT

Hi,

Just check for the Date format.

Regards,

Kaushik Solanki

Re: Showing only last 12 month values in a chart

rsdhavle — Thu, 07 Aug 2014 07:36:25 GMT

Date format is DDMMYYYY

Re: Showing only last 12 month values in a chart

kaushiknsolanki — Thu, 07 Aug 2014 07:38:23 GMT

Hi,

Is it sure a Date or a text field.

Just try this test to know that.

Click anyone date and in text box put '=Num(Date)'

If you see number, then its a date, else its a text.

Kindly let us know.

Regards,

Kaushik Solanki

Re: Showing only last 12 month values in a chart

Gysbert_Wassenaar — Thu, 07 Aug 2014 07:38:31 GMT

Can you post a document with some data? That way we won't have to keep guessing.

Re: Showing only last 12 month values in a chart

rajat2392 — Thu, 07 Aug 2014 10:54:42 GMT

Hi Rohan,

As far as I understand the scenario, you have the dimension as month. So if you calculate suppose revenue and give the month as dimension in chart, the revenue will be calculated for all 12 months. Now suppose you have four years data (2010,2011,2012,2013), so for Jan, it will show the sum of Jan for all 4 years.

I suggest, better to use MonthName (Jan 2013, Feb 2013...) for better understanding.

If you use this MonthName dimension, you can restrict it easily then for last 12 months, and it will be more interpretable that which month and year it is showing.

Regards

Rajat