Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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

23 Replies
sureshqv
Esteemed Contributor III

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

MVP
MVP

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

MVP
MVP

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.

Not applicable

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

Not applicable

Re: Calculating last 6 months based on selected month

Hello Jonathan,

I have added a sample.

Many Thanks,

Hasvine

Not applicable

Re: Calculating last 6 months based on selected month

Hello Jonathan,

I have added a sample.

Many Thanks,

Hasvine

Highlighted
MVP
MVP

Re: Calculating last 6 months based on selected month

Try expression like:

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

PFA

Edit: Corrected

MVP
MVP

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.