Calculating last 6 months based on selected month

[#1]

Hello All,

I would like to know if it is possible to automatically calculate last 6 months based on my selected month:

For example if i choose Jan 2016

How can I get make my graph automatically to show:

janv-2016

Dec-2015

Nov-2015

Oct-2015

Sept-2015

Aug-2015

Any idea?

Many Thanks,

Hasvine

[Chanty4u]

try dis

COUNT(

               {$<

                    Order Month={"$(=month(addmonths(today(),-6)))"},

                    Order Year={"$(=year(addmonths(today(),-6)))"}

               >}

[Order ID])  

Would be better if you transform your varchar month to number and you can use this one:

COUNT(

               {$<

                    Order MonthNum={"$(=num(month(addmonths(today(),-6))))"},

                    Order Year={"$(=year(addmonths(today(),-6)))"}

               >}

[Order ID])  

[tresesco]

Any idea?

Many ideas could be there. You just have to bear a little hassle of sharing a sample data set /qvw and probably explain the expected output a bit more (may be with context).

[Kushal_Chawda]

see this

Calculating rolling n-period totals, averages or other aggregations

[jonathandienst]

You need to ensure that the month field contains date values (formatted as months), or that you include a month sequence field. In either case, you can use simple date calculations in the set expression to produce the result. You will also need to mask the date selection.

For example, define a month sequence in the load:

     LOAD ...

          trans_date,

          Year(trans_date) as Year,

          Month(trans_date) as Month,

          (Year(trans_date)-2000)*12 + Month(trans_date) as MonthSequence,

          ...

Then use:

     Sum({<MonthSequence = {">=$(=Max(MonthSequence)-6)<=$(=Max(MonthSequence))"}>} Amount)

You will also need to mask the date selection:

     Sum({<MonthSequence = {">=$(=Max(MonthSequence)-6)<=$(=Max(MonthSequence))"},

     Month, Year, trans_date>} Amount)

I suggest that you upload a sample for more specific help.

[#6]

Hello All,

I have attached a small sample. What I need is that when for example i select '12' my chart automatically shows the data for 7,8,9,10,11 and 12.

Many thanks for your help.

KR,

Hasvine

[#7]

Hello Jonathan,

I have added a sample.

Many Thanks,

Hasvine

[#8]

Hello Jonathan,

I have added a sample.

Many Thanks,

Hasvine

[tresesco]

Try expression like:

Sum({<Month={'>=$(=Max(Month)-5)<$(=Max(Month))'}>}Amount)

PFA

Edit: Corrected

[jagan]

Hi,

Try this expression using set analysis.

=Sum({<Month={'>=$(=Max(Month) - 5)<=$(=Max(Month))'}>}Amount)

Regards,

Jagan.