23 Replies Latest reply: Mar 22, 2016 3:35 AM by Hasvine Dhurmea

# Calculating last 6 months based on selected month

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

• ###### Re: Calculating last 6 months based on selected month

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])

• ###### Re: Calculating last 6 months based on selected month

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).

• ###### Re: Calculating last 6 months based on selected month

Hello Jonathan,

I have added a sample.

Many Thanks,

Hasvine

• ###### Re: Calculating last 6 months based on selected month

I would load like this to correctly handle data that spans over more than one year:

SalesData:

LOAD

Date(MakeDate(Year, Month), 'MM-YYYY') as Month,

Year*12 + Month as MonthSequence,

Year,

Amount

inline [

Year, Month, Amount

2015,8,7

2015,9,8

2015,10,6

2015,11,9

2015,12,7

2016,1,6

2016,2,4

2016,3,7

2016,4,3

2016,5,4

2016,6,9

2016,7,5

2016,8,7

2016,9,8

2016,10,6

2016,11,9

2016,12,7

];

And use the expression:

Sum({<MonthSequence = {">\$(=Max(MonthSequence)-6)<=\$(=Max(MonthSequence))"}, Month, Year>} Amount)

• ###### Re: Calculating last 6 months based on selected month

Hi Jonathan,

Actually my real dates are in DD/MM/YYYY so do I need to seperate the Year?

Thanks,

Hasvine

• ###### Re: Calculating last 6 months based on selected month

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.

• ###### Re: Calculating last 6 months based on selected month

Hello Jonathan,

I have added a sample.

Many Thanks,

Hasvine

• ###### Re: Calculating last 6 months based on selected month

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

• ###### Re: Calculating last 6 months based on selected month

Try expression like:

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

PFA

Edit: Corrected

• ###### Re: Calculating last 6 months based on selected month

The problem is that if i choose the month 10 for example it gives me the months 5 to 12.

It does not stop at the month 10. :-(

• ###### Re: Calculating last 6 months based on selected month

check the updated expression :

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

• ###### Re: Calculating last 6 months based on selected month

Hi,

Try this expression using set analysis.

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

Regards,

Jagan.

• ###### Re: Calculating last 6 months based on selected month

See this example:

• ###### Re: Calculating last 6 months based on selected month

It doesnt help in my situation unfortunately.

Thanks,

Hasvine

• ###### Re: Calculating last 6 months based on selected month

Is this you need?

• ###### Re: Calculating last 6 months based on selected month

Sorry, if you need 6 month, change in the formula 5 for 6

• ###### Re: Calculating last 6 months based on selected month

I need the first table.

Can you please explain how you did it.

Many Thanks,

Hasvine

• ###### Re: Calculating last 6 months based on selected month

OK,

in dimension are Month and in the expression put this:

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

• ###### Re: Calculating last 6 months based on selected month

if you need include selected month use this:

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

• ###### Re: Calculating last 6 months based on selected month

Hi,

Try this expression

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

Regards,

Jagan.

• ###### Re: Calculating last 6 months based on selected month

Helpful. Thanks.