How to display in the dashboard the data for last 9 months ignoring the data prior to 9 months.

[#1]

Hi Guys,

Actually i want to display the data only for the last 9 months even if i have the data from many years and preferably i want it to be done in script level( if not then can be done in expression level).

Plz find the details below:

   (In this case i want to display data from Nov-2014 to Jul-2015)

Month_YEAR	Total Likes
Jul-14	700
Aug-14	400
Sep-14	500
Oct-14	530
Nov-14	560
Dec-14	590
Jan-15	620
Feb-15	650
Mar-15	680
Apr-15	100
May-15	20
Jun-15	481
Jul-15	36

Guys plz help me out.............

[swuehl · Accepted solution]

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

LOAD Date#(Month_YEAR,'MMM-YYYY') as Month_Year,
    [Total Likes]
FROM
[https://community.qlik.com/thread/171218]
(html, codepage is 1252, embedded labels, table is @1)
WHERE Date#(Month_YEAR,'MMM-YYYY')> Monthstart(today(1),-9);

[#3]

Thanks Swuehl  for help and quick response .

Can you plz help me out to do this in expression level.

[swuehl]

In a chart with dimension Month_Year, it should be something like

=Sum({<[Month_Year] = {">=$(=Monthstart(today(),-9))"}>} [Total Likes])

[sunny_talwar]

In the expression you should be able to restrict all your expressions using set analysis using this:

{<Date = {"$(='>=' & Date(AddMonths(Max(Date),-8)))"}>}

Here I created Date in the script using the Month_YEAR field you gave above like this:

Table:

LOAD MonthName(Date#(Month_YEAR, 'MMM-YY')) as Month_YEAR,

  Date(Date#(Month_YEAR, 'MMM-YY')) as Date,

    [Total Likes]

FROM

[https://community.qlik.com/thread/171218]

(html, codepage is 1252, embedded labels, table is @1);

HTH

Best,

Sunny