Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
MVP
MVP

Re: Calculating last 6 months based on selected month

YOu can also check for various similar expressions from below link

Set Analysis for Rolling Periods

Regards,

Jagan.

Not applicable

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. :-(

ecolomer
Honored Contributor II

Re: Calculating last 6 months based on selected month

See this example:

Not applicable

Re: Calculating last 6 months based on selected month

It doesnt help in my situation unfortunately.

Thanks,

Hasvine

ecolomer
Honored Contributor II

Re: Calculating last 6 months based on selected month

Is this you need?

p_roll.png

MVP
MVP

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)

ecolomer
Honored Contributor II

Re: Calculating last 6 months based on selected month

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

Not applicable

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

ecolomer
Honored Contributor II

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)

MVP
MVP

Re: Calculating last 6 months based on selected month

check the updated expression :


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