topic Re: Last 3 months in Set Analysis in Line Graph in QlikView

Last 3 months in Set Analysis in Line Graph

dmohanty — Thu, 05 Mar 2015 22:49:58 GMT

Hi All,

I have a Line Graph, where I need to show Last 3 Months (from a Set of 13 Months) Data i.e. Dec-14 , Jan-15, Feb-15

My Month/Year field has data n this format: MMM-YY.

Could you please help?

Re: Last 3 months in Set Analysis in Line Graph

maxgro — Thu, 05 Mar 2015 23:36:32 GMT

mmmy is the month year field

v the measure

=sum( {$<mmmy={'$(=date(addmonths(max(date#(mmmy, 'MMM-YY')),-0), 'MMM-YY'))',

'$(=date(addmonths(max(date#(mmmy, 'MMM-YY')),-1), 'MMM-YY'))',

'$(=date(addmonths(max(date#(mmmy, 'MMM-YY')),-2), 'MMM-YY'))'

}>} v )

Re: Last 3 months in Set Analysis in Line Graph

sunny_talwar — Fri, 06 Mar 2015 00:03:38 GMT

Hi,

I have created a flag in the script to do what you wanted to achieve. See if this is helpful:

Table:

LOAD *,

MonthName(Date) as [Month-Year];

LOAD * INLINE [

Date, Value

01/01/2014, 90

01/01/2014, 80

02/01/2014, 100

02/01/2014, 40

03/01/2014, 80

03/01/2014, 120

04/01/2014, 76

04/01/2014, 90

05/01/2014, 56

06/01/2014, 90

06/01/2014, 56

07/01/2014, 57

07/01/2014, 56

08/01/2014, 90

08/01/2014, 56

09/01/2014, 120

09/01/2014, 56

10/01/2014, 130

10/01/2014, 56

11/01/2014, 190

11/01/2014, 56

12/01/2014, 180

12/01/2014, 56

01/01/2015, 120

01/01/2015, 56

02/01/2015, 110

02/01/2015, 56

];

Table1:

LOAD [Month-Year],

Sum(Value) as [Total Value],

If(RowNo() < 4, 1, 0) as [3MonthFlag]

Resident Table

Group By [Month-Year]

Order By [Month-Year] desc;

DROP Table Table;

Line Chart:

If this look helpful, then PFA the application where I did this.

Best,

Re: Last 3 months in Set Analysis in Line Graph

dmohanty — Fri, 06 Mar 2015 01:08:57 GMT

Hi Massimo,

Thank you for the help.

But If a value from the Month Field is selected, the chart is getting populated as per selection. The chart would always show that Last 3 months, irrespective of Selection.

Adding the 'Month = ,' didn't helped. ANy more suggestions please!

Re: Last 3 months in Set Analysis in Line Graph

dmohanty — Fri, 06 Mar 2015 01:26:10 GMT

Ok Massimo,

I guess the use of Max(Month Field) was not allowing to restrict the display upon field selection.

I changed it to like this below:

{'>=$(=Max(Monthstart(AddMonths(Today(),-3))) ) <$(=Max(Addmonths(MonthEnd(Today()),-1)))'}>}

Its working and independent of MonthYear selection too.

Thank you again!

Re: Last 3 months in Set Analysis in Line Graph

dmohanty — Fri, 06 Mar 2015 01:26:44 GMT

Hi Sunindia,

Thank you very much for the help. This helped in achieving my result.

Re: Last 3 months in Set Analysis in Line Graph

sunny_talwar — Fri, 06 Mar 2015 01:50:57 GMT

Not a problem. Always there to help.

Have a good one.

Best,