Show rolling 3 months on bar chart

[Anonymous]

I am trying to display the rolling 3 months on a bar chart when you select a month and year.  Example:  Select June 2014 and display the months of April, may and June.  I have tried several different solutions (some provided from some of the posts on this site), but nothing seems to work.

I have attached a QlikView file showing the chart and date selections along with an Excel file of the test data.  When I select a Month, it only show the selected month and not the 2 prior months.  If I select the year without a month, all the months appear instead of the last three months only.  You will also see a text box below the chart that displays the values of the variables I use in the expression.  I can see that the variables are calculating the correct values when a month is selected, but they do not seem to be affecting the chart.

Does anyone have a solution.for this?

Thanks.

Ed

[#2]

Try using Set Analysis with months < than selected month-3.

[#3]

So I changed:

MASTERCALENDAR:

LOAD

  TEMPDATE AS DATE,

    MONTH(TEMPDATE) AS MONTH,

    NUM(MONTH(TEMPDATE)) AS MONTH_NUMERIC,   

    YEAR(TEMPDATE) AS YEAR,

    'Q' & CEIL(MONTH(TEMPDATE) / 3) AS QUARTER,

    'Q' & CEIL(MONTH(TEMPDATE) / 3) &'-'& YEAR(TEMPDATE) AS QUARTER_YEAR,

    DUAL( WEEKSTART(TEMPDATE, 0 ,-2) & ' - ' & WEEKEND(TEMPDATE, 0 ,-2), WEEKSTART(TEMPDATE, 0 ,-2)) AS WEEK,

    DATE(MONTHSTART(TEMPDATE),'MMM-YYYY') AS MONTHYEAR,

  INYEARTODATE(TEMPDATE, $(vMAXDATE), 0) *  -1 AS CURYTDFLG,

  INYEARTODATE(TEMPDATE, $(vMAXDATE), -1) *  -1 AS LASTYTDFLG

Resident TEMPCAL

ORDER BY TEMPDATE ASC;

your vMaxMonth Variable into: =Max(MONTH_NUMERIC)

and last but not least:

=Sum ({$<MONTH_NUMERIC = {">=$(=vMaxMonth-3)"}>}Balance)

Have Fun withit

[maxgro]

try to start from this expression (see attachment)

= Sum ({$<EOM_DATE={">=$(=AddMonths(MonthStart(vSelectedMaxDate),-2))<=$(=MonthEnd(vSelectedMaxDate))"}, MONTH=,QUARTER=,YEAR=>}Balance)

another llittle but useful thing

if you remove (blank) the expression label you can see the set analysis expression

[Anonymous]

Thanks, Patrick and Massimo.

Both solutions work well in the test file I sent.  I am still having a problem with my live data (still only showing a single month), but this gives me more to work with.  It may how the live data date field is defined.

Ed

[Anonymous]

I found and corrected the issue in my live file.   When I defined the date in the load statement I had it defined as:

DATE(EOM_DATE,'M/D/2014') AS EOM_DATE    instead of     DATE(EOM_DATE,'M/D/YYYY') AS EOM_DATE.

This did not cause an error in loading the data, BUT - it caused my expression not to work.

Thanks again for all your help.

Ed